RE: [coldbox:11945] SQL DeadLock

I did come across a blog that fitted what I was sort of doing and it said use the isolation serializable, and the update lock,

For what it’s worth, the only difference between serializable and repeatable read is range locks that prevent phantom reads. For instance, you select out all records where quantity is less than 5, and after your transaction starts, a NEW record is inserted by a second transaction in the table that has a quantity of less than 5. Repeatable read will allow that, but serializable will not since the range lock locks that “range” of values in the table as opposed to specific rows. Unless you are using <, >, or BETWEEN in your where clause, you probably don’t need serializable.

but ColdFusion doesn’t seem to have that as the transaction.

Not sure what you mean. The docs show that serializable is a valid isolation level for a transaction.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c6b.html

So I was hoping you might have some code sample I can see.

Selecting with an update lock (in MS SQL) looks like this:

SELECT column
FROM table WITH(UPDLOCK)

An isolation level higher than read-committed (which is the default for SQL Server 2000, 2005, and 2008) is unecessary as long as you are in a transaction and using an update lock, but it should look like this (I’m still on CF8, so I’ve never actually used the script version):

transaction isolation=“serializable”
{

}

Thanks!

~Brad

kk, it seems I was trying to confuse myself.

So if I understand that then, if I apply what you are suggesting then it will not update until select has finished.

Would the select and update happen in one transaction? I am worried that just before the update another request comes in and does a select, then I am right back to square one. But I am assuming that from what you are saying this will fix that issue.