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

Xiangyang Liu

I don't mean disconnect all clients, other clients just need to wait until the transaction is done, like a "critical section" as I described below.

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

Chris Meech

Is the transaction being executed at the client side or server side(within an sp for example)? If at the client side, then you're going to need some kind of notification system to coordinate the operation. If at the server side, then you at least need to put the transaction into a stored procedure or function, but I don't know how you would limit client access to it so that when one client makes a call to it, all other clients block until the first call completes.

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]

Xiangyang Liu

It is on the server side and it is within a stored procedure.

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

David Skelly · modified on Friday, December 18, 2009 10:10 AM

Create a "lock" table with one row in it. At the beginning of your transaction select this row for update. If you run the transaction with an isolation level of serializable then it will lock the row exclusively to your transaction. Any other transaction that tries to access the same table will block until (a) your transaction commits or (b) your transaction rolls back or (c) it times out.

swjam · modified on Friday, December 18, 2009 10:10 AM

how about using one of those global @@ variables?

---------------------------------------------------------- Lorem ipsum dolor sit amet.

realJSOP

swjam wrote:

@@

I think the official name for that is the "nipples" prefix...

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Steve Westbrook · modified on Friday, December 18, 2009 10:10 AM

Take a look at the Service Broker[^] in SQL Server 2005. It looks like queues might do what you're looking for, specifically using the receive[^] command in conjunction with create queue.[^]. The queue will execute only one command at a time, so just add the appropriate sp calls to it, unless you could potentially be getting the same commands (which you want to block) from parts of the code you don't control, in which case the whole thing is moot.

Jorgen Sigvardsson · modified on Friday, December 18, 2009 10:10 AM

What exactly are you doing that requires a critical section!? All operations on data within the scope of a transaction is by definition atomic. If you are trying to execute code of some sort that really doesn't adhere to the rules of MSSQL/ACIDness, such as calling into .NET assemblies, then you'll have to handle synchronization in the .NET assemblies. However, pay attention to what you're doing. Have you any idea what happens if the SQL engine is blocked by external code, possibly indefinitely, by a dead lock introduced by the external assembly? I have no idea, but I reckon it's bad for apps and database manager... :)

-- Kein Mitleid Für Die Mehrheit

Jorgen Sigvardsson

I think they look like the nose holes of that fat cop in Simpson...

-- Kein Mitleid Für Die Mehrheit

Roger Wright · modified on Friday, December 18, 2009 10:10 AM

Would it help to set the Isolation Level for the transaction to Serializable[^]? I would not suggest doing this a lot, as it seems to me that it would cause a performance hit, but for this critical operation it might make sense.

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

modified on Friday, December 18, 2009 12:38 PM

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]