Locking A Query?

[JoeD]

Greetings. I need some feedback on using cflock with a query. I essentially
want to single thread just one piece of code (a query) on a page.

The query is a SQL stored procedure that does this:
1. Select an id from the next available record in the table.
2. Update that record (to make it no longer available).
3. Return the results of that record.

This happens pretty quick, but if two users hit the page at exactly the same
time, they can both get the same id. The update doesn't happen before the next
user runs executes the first select statement. Obviously, that's no good.

My questions: Would wrapping the query in cflock single-thread that code and
eliminate this issue (by forcing the query to complete for one user before a
second user can execute that query)?

Are there better solutions? Should I instead be looking at the stored proc by
changing the transaction isloation level?

Oh, yeah ... we're running CFMX, SQL 8, IIS on Windows Server 2003.

TIA.
joe

[Sojovi]

Always is better put the DB operations in a stored procedure, the reasons are
well known. Using CFLOCK is a solution but you are not really locking the DB,
only a portion or CF code.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT ..... (Select an id from the next available record in the table.)
UPDATE ..... (Update that record (to make it no longer available).
SELECT ...... (Return the results of that record.)

COMMIT TRANSACTION

Regards

[JoeD]

Thanks Sojovi -

I was thinking this must be so. SQL warns of using serializable isloation
because of performance hit, but it's most important to get it correct, even if
performance has to suffer a bit.

Is it safe to assume that modifying the isolation level in the stored proc is
still more efficient than using cflock?

jd

[Sojovi]

The point is : With CFLOCK you are locking the portion of CF code beetwen begin
and end tag, but you are not really locking the DB, because if at the same time
CF is processing the CFLOCK somebody using the SQL query analizer modifies that
records involved, you'll have a consistency problem.

But if you want to lock more code than only the SQL statements you'll need to
use CFLOCK (besides SET ISOLATION LEVEL).

With you SET ISOLATION LEVEL SERIALIZABLE you are sure that nobody can access
the records involved in the opened transaction.

Regards

Regards

[JoeD]

Gotcha. That makes sense, because if I just set the isolation level to
serializable without using cflock, I get deadlocks. I'm hoping that setting
the isolation level to serializable in SP and locking the CF code with cflock,
I'll get the results I hope for.

Thanks for your help.

best,
joe