How do I make a transaction an "atomic" operation? (SQL Server 2005) [modified]

David Skelly

Jörgen Sigvardsson wrote:

All operations on data within the scope of a transaction is by definition atomic.

Atomic yes, but not necessarily serializable, which seems to be what the OP is after. That depends on the isolation level of the transaction.

David Skelly · modified on Friday, December 18, 2009 12:38 PM

Roger Wright wrote:

I would suggest doing this a lot, as it seems to me that it would cause a performance hit

I think you mean "would not suggest" here.

Luc Pattyn

about sig3: that's no pet, this is[^]. :laugh:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Roger Wright

Oops... You're entirely correct! :-O There's cow-orkers milling about this place, so I had to type fast...:~

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Luc Pattyn

and you probably need more time to get used to the new monitor... :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Xiangyang Liu

What I want in the transaction: 1. Check if a database record's status field is "pending". 2. If it is, then set status field to "processing", then process it. Otherwise, don't do anything. 3. After processing, set the status field to "processed". If the above code is in a "critical section", then I can guarantee only one client is processing a record. I thought putting the code within a transaction will solve the problem. Apparently not.

My .NET Business Application Framework My Home Page My Younger Son & His "PET"

Xiangyang Liu

Thanks. Your solution is definitely workable, a little more complicated than I prefer, but there is no other choice for me right now. Thanks again.

My .NET Business Application Framework My Home Page My Younger Son & His "PET"

Xiangyang Liu · modified on Friday, December 18, 2009 12:38 PM

Roger Wright wrote:

Would it help to set the Isolation Level for the transaction to Serializable?

That's exactly what was suggested by our DBA. Howver, there is still a chance to screw up. Here is what is within the transaction: 1. Check if the record's status field is "pending". 2. If it is, then set it to "processing" and process it, otherwise return. By reading the documentation about "serializable", it seems possible for two different clients to find the status field to be "pending", and each setting it to "processing" and each processing the same record. However, setting isolation level to "seriablizable" greatly reduced the chance of two clients processing the same record. Let me know if I am wrong on this. Thanks.

My .NET Business Application Framework My Home Page My Younger Son & His "PET"

Roger Wright

That's at home, and yes, it's taking some getting used to. :laugh: I never realized just how wrong it looks to have square cards for solitaire! Worse, I think a recent Windows Update must have made it harder; I haven't won a game in days!!!

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Chris Meech

:doh: I think I just heard the '70s call and they want the term batch processing brought back. :cool:

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Jorgen Sigvardsson

I suspect an UPDATE could be of help, since they are atomic, much like an interlocked increment.

UPDATE table SET status = 'processing' WHERE status = 'pending' AND PrimaryKey = @value;
if row count > 0
-- Do the processing
else
-- Some other client is processing...
end if

-- Kein Mitleid Für Die Mehrheit

Xiangyang Liu

Chris Meech wrote:

they want the term batch processing brought back.

I hardly noticed they were gone. When did that happen? For how long? :-D

My .NET Business Application Framework My Home Page My Younger Son & His "PET"

Xiangyang Liu

Ok, this could be another solution, and simpler, too. Thanks.

My .NET Business Application Framework My Home Page My Younger Son & His "PET"

Roger Wright

According to MSDN,

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

It sounds to me like you would be fairly safe with this setting. Certainly it would be safer than the default.

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Roger Wright

Yup, much easier! :-D

"A Journey of a Thousand Rest Stops Begins with a Single Movement"