Oracle Database Lock Issue

[CFRAM]

Hi,

I have a SQL statement that I execute obn Oracle 9i Database:

select lnum from parolee
Where lNUM='#trim(UCase(Form.fnum))#'
FOR UPDATE NOWAIT

The Oracle is supposed to lock and throw an error if another user tries to get
the same record. I am not sure whether the Oracle is not processing the
statement or CF App. Server is not passing to the DB server. However the same
statement works when we use TOAD.

Thanks

[jonwrob]

How do you know it's not working? Some more code might help. I ask because I'm having a hard time trying to come up with a reason for locking a record in this manner in a web environment.

JR

[CFRAM]

This how I found its not working properly. As USER1 I login into the
application and I go to the search page where I give the parameter for the SQL
and submit. The SQL statement all the values from the table and assue that It
locks the row. Login as USER2 and repeat the same steps. As USER2 I get the
same row for the same parameter. Now this is not good since USER1 has already
aquired the lock the DB should give an error.

If I use TOAD then I get the desired result. I get into TOAD and I give the
SQL statement with the parameter and lock the record. When USER1 logs into the
application he gets the error message as desired.

Why there are two different behaviour ?

Thanks

[jonwrob]

OK, this is easy. Have you ever noticed that you don't need to explicitly
commit a DML statement in a CFQUERY tag, even if you haven't included the
CFQUERY in a CFTRANSACTION tag? This must mean that CF is issuing a commit
upon successful completion of the query. I would therfore guess that CF is
issuing a commit upon successful complete of your select for update query, thus
releasing the lock immediately after obtaining it.

The way to prevent CF from issuing the commit after the CFQUERY is to put it
in a CFTRANSACTION tag, but this doesn't do what you want. You want to lock
the record until after an entirely different request is run. Not only do I not
think it's possible to lock a database resource like this, I would STRONGLY
recommend against it. You will constantly have to do things like kill sessions
in order to release locks that your application failed to release.

A better solution would be to implement the locking features on the CF side,
perhaps by storing the primary key values of the records you wish to lock in an
application variable (or structure).

JR

[CFRAM]

So we can use <CFLOCK> ?

[paross1]

I think that you may be misunderstanding the use of FOR UPDATE and NOWAIT. if
you include the FOR UPDATE clause in your select, then you place a row level
lock on on all rows selected until you issue a COMMIT or ROLLBACK. Any other
attempt to update those rows by other users will wait until you COMMIT or
ROLLBACK, or until you get a DEADLOCK. By including the NOWAIT keyword, all
that you are doing is to tell Oracle not to wait if the tables that you are
selecting are already locked by another user, in which case it will raise an
exception and give you an Oracle error.. It is not intended to tell you if
another user is attempting to update your locked rows. If you use FOR UPDATE
NOWAIT within a stored PL/SQL procedure, then you could handle your exception,
and return an error message, for example, that says that your transaction could
not be completed, etc.

Phil

[jonwrob]

CFLOCK prevents other users from running the same code.

You want to allow different users to run the same code for different records. CFLOCK can't help you.

JR