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

[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"