SELECT & UPDATE: locking question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT & UPDATE: locking question

Author

Topic

CarneyALansford
Starting Member

2 Posts

Posted - 2009-03-03 : 02:08:13

I'm trying to build a stored procedure which can be executed simultaneoulsy many times on the same PaymentID, and I want to avoid each of these instances affecting one another.

My question is, will a combination of locking hints suffice (I'm thinking WITH (UPDLOCK, SERIALIZABLE))? Or is it necessary to use a transaction isolation level of REPEATABLE READ? And, if I use REPEATABLE READ, two questions:

1) will other transactions that attempt to interact with these rows just hang until this transaction completes? or will they fail with some errorcode of some kind?

2) Will I only be able to read rows affected by this transaction using (nolock) while the txn is under way?

I'm looking for the most concurrency that is possible, while guaranteeing that the below transaction is executed without fail.

BEGIN TRANSACTION

-- If the payment is already being processed by another instance, bail out
-- 1 = PAYMENT IN PROCESSING
IF EXISTS(SELECT * FROM Payments WHERE Status = 1) RETURN

UPDATE Payments
SET Status = 1 -- 1 = PAYMENT IN PROCESSING
WHERE PaymentID = @PaymentID

.. do the processing of the payment here ..

UPDATE Payments
SET Status = 2 -- 2 = PAYMENT SUCCESSFULLY PROCESSED

COMMIT TRANSACTION

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-03 : 03:04:20

To keep a transaction isolated from others you just want to specify the locking hits or SQL Server's predefined Isolation levels and not need and custom logic. As you want that changes by one users must be saperated from others then only starting a Transaction (with BEGIN TRANACTION)in the starting of data change is enough without any lock hint (in default READ COMMITTED isolation level).
Lock timeout error is returned after waiting longer then time specified in @LOCK_TIMEOUT setting.
NOLOCK hint will display changes made by any transaction. This hint is to not honour any lock and not place any lock during read operation.

CarneyALansford
Starting Member

2 Posts

Posted - 2009-03-03 : 03:26:27

quote:
Originally posted by subhash chandra

To keep a transaction isolated from others you just want to specify the locking hits or SQL Server's predefined Isolation levels and not need and custom logic. As you want that changes by one users must be saperated from others then only starting a Transaction (with BEGIN TRANACTION)in the starting of data change is enough without any lock hint (in default READ COMMITTED isolation level).
Lock timeout error is returned after waiting longer then time specified in @LOCK_TIMEOUT setting.
NOLOCK hint will display changes made by any transaction. This hint is to not honour any lock and not place any lock during read operation.

Are you saying that this article is incorrect then?
http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

that article seems to say that just a txn is not enough. thoughts?

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-03 : 05:12:23

Every scenario can not be satisfied in a single paragraph. If you have any example or scenario to need explanation then specify.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-03 : 06:45:28

For maximum throughput you may want to look at using an application lock:

DECLARE @Resource nvarchar(255)
SET @Resource = N'LockPaymentId' + CAST(@PaymentID AS nvarchar(20))

BEGIN TRANSACTION
    EXEC sp_getapplock @Resource, N'Exclusive'
    -- no need to set status to 1
    
    -- payment processing

COMMIT

guzmanda
Starting Member

2 Posts

Posted - 2009-03-03 : 20:02:16

quote:
Originally posted by CarneyALansford

I'm trying to build a stored procedure which can be executed simultaneoulsy many times on the same PaymentID, and I want to avoid each of these instances affecting one another.

My question is, will a combination of locking hints suffice (I'm thinking WITH (UPDLOCK, SERIALIZABLE))? Or is it necessary to use a transaction isolation level of REPEATABLE READ? And, if I use REPEATABLE READ, two questions:

1) will other transactions that attempt to interact with these rows just hang until this transaction completes? or will they fail with some errorcode of some kind?

2) Will I only be able to read rows affected by this transaction using (nolock) while the txn is under way?

I'm looking for the most concurrency that is possible, while guaranteeing that the below transaction is executed without fail.

BEGIN TRANSACTION

-- If the payment is already being processed by another instance, bail out
-- 1 = PAYMENT IN PROCESSING
IF EXISTS(SELECT * FROM Payments WHERE Status = 1) RETURN

UPDATE Payments
SET Status = 1 -- 1 = PAYMENT IN PROCESSING
WHERE PaymentID = @PaymentID

.. do the processing of the payment here ..

UPDATE Payments
SET Status = 2 -- 2 = PAYMENT SUCCESSFULLY PROCESSED

COMMIT TRANSACTION

Dan Guzman
SQL Server MVP

guzmanda
Starting Member

2 Posts

Posted - 2009-03-04 : 08:46:17

Sorry for the quote-only response. There's a good reason I prefer newsgroups over web-based forumns :-)

Locking hints effectively allow you to adjust the isolation level per-statement. You can run in the lowest possible suitable isolation level to maximize concurrency yet escallate on a given statement with locking hints for particular needs.

There's too much code missing from your snippet for me to fully understand your full intentions so let me make some observations.

quote:

1) will other transactions that attempt to interact with these rows just hang until this transaction completes? or will they fail with some errorcode of some kind?

Assuming Status 1 rows exist only while a transaction is in progress, the IF EXISTS short-circuit test will be blocked if any rows qualify. It is also posssible that concurrent sessions could both find that no rows qualify and proceed to the first UPDATE. I can't say what the exact behavior might be without seeing the rest of your code but a deadlock is a possibility due to the REPEATABLE READ isolation level. In contrast, READ_COMMITTED probably wouldn't deadlock since locks would be released after IF EXISTS check.

If you add the UPDLOCK,SERIALIZABLE or UPDLOCK,HOLDLOCK to the IF EXISTS SELECT and run in the READ COMMITTED or REPEATABLE READ isolation level, then that lock will serve as a gate to the subsequent code.

quote:

2) Will I only be able to read rows affected by this transaction using (nolock) while the txn is under way?

Updated rows will require NOLOCK (or UNCOMMITTED transaction isolation level).

quote:

I'm looking for the most concurrency that is possible, while guaranteeing that the below transaction is executed without fail.

You can maximize concurrency by running in READ COMMITTED and rely on row locking. If you SELECT with the intention of updating, add the UPDLOCK and SERIALIZABLE/UPDLOCK to only those selects. Setting the READ_COMMITTED_SNAPSHOT database option will improve concurrency further by using row versioning but you need to ensure you have adequate tempdb resources before doing so.

Dan Guzman
SQL Server MVP

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-04 : 11:51:52

SQL Server automatically applies row level locking for update.
If your "do the processing of the payment here" is an update in same row and same table as where you set status then - you can combine setting status to finished (2) and do other other UPDATEs in same statement - if you add a check for status finished. You should not get any locks in this case.

UPDATE Payments
SET Status = 2, ColA=5, ColB=6,ColC=7
WHERE PaymentID = @PaymentID and Status <> 2

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 12:56:12

thanks for this info y'all. May I ask a follow up question here? I have an ssis package that runs every 15 minutes. The users are hitting hard the same table the SSIS is INSERTing INTO. How would you handle this situation priority is for the SSIS package. How should I go about avoiding any conflicts.

MErci

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources