• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2456
  • Last Modified:

SQL server locking queries

Dear All,

I am working on a multi-user application. I have a stored procedure that will be accessed by multiple users at the same time. My stored procedure reads a value from a single row of a table and updates the row later after doing some processing. When one user reads the row of the table, till he/she updates the row, no other user should be allowed to even read that row of the table but to achieve parallel processing, other users can work on other rows.

I am using following locks to achieve consistency and parallelism:
1. rowlock - to achieve parallel processing
2. xlock - to achieve mutual exclusion - when one user reads a row, no other user is allowed to read the same row.
3. holdlock - holds the above locks until user updates the row

code snippet is given below:

BEGIN TRANSACTION
.
.
.
select  @SNId = b.Id , @PartID = a.Id , @DeviceID = a.Device_Id , @CurrSN_1 = b.CurrSerialNo_1 , @CurrSN_2 = b.CurrSerialNo_2_OR_EndSN
from     wps_part_number   a  with (nolock) join   wps_serial_number  b  with (rowlock , xlock , holdlock) on a.Id = b.PartID  where a.Part_Label = @ModelLabel
if  @@ROWCOUNT = 0
begin
      ROLLBACK TRANSACTION
      raiserror('....',16,1)
      return
end

.
.
.

update wps_serial_number with (rowlock , xlock , holdlock) set CurrSerialNo_1 = @Quantity + 1  ,   CurrSerialNo_2_OR_EndSN = null  where id = @SNId;
if @@Error <> 0
begin
           ROLLBACK TRANSACTION
            raiserror('....',16,8)
           return
end
insert into wps_SN_Allocation_History ( PartID , DistID , StartSN , EndSN , AllocationDate ) values ( @PartID , @DistID , 1 , @Quantity , getdate() )
if @@Error <> 0
begin
      ROLLBACK TRANSACTION
      raiserror('.....,16,8)
      return
end

.
.
.

if @@error = 0
      COMMIT TRANSACTION
else
      ROLLBACK TRANSACTION

I am using a transaction beacuse I have to hold a lock until the transaction commits. The main table in the stored procedure is wps_serial_number on which I have to apply locking. After processing wps_serial_number I have to insert a row in wps_sn_allocation_history table and all these tasks should be done as an atomic operation.

Now, i have the following problems/queries:

1. Even I am locking a single resource i.e. wps_serial_number table, yet i am getting the following message when i run the stored procedure using a multi-threading application:

Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Because i am locking on a single resource, i should not get any deadlock.

2. As i am using wps_serial_number table in 2-3 queries but the same row each time. Do i need to apply all the locks each time i run a query on wps_serial_number table.
As i am using holdlock option, i think i need to mention the locks in the first query only.

3. Do i need to mention the locks in insert operation also for a multi threading application where multiple threads try to insert in a table at the same time or sql server takes care of locking itself while insertion.

Can anybody please tell me a better option in terms of locking or isolation level for the above application or please let me know what and where i am doing wrong.

Waiting for your responses...

Thanks in anticipation,
Regards,
Sorabh Kumar

 
0
Sorabh2
Asked:
Sorabh2
2 Solutions
 
SandeepratanCommented:
JUST USE ---
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

ALONG WITH THE BEGIN TRANSACTION IN UR STORED PROC
AND NO NEED TO USE THE LOCKING HINTS

SECONDLY U DONT NEED TO USE LOCKING HINTS FOR INSERT QURIES,

REGDS
SANDEEP
0
 
dqmqCommented:
I agree, you are making it too complex.  The basic idea is to set the desired transaction isolation level and let SQL Server take care of the necessary locking. However, I do not think REPEATABLE READ is sufficient. REPEATABLE READ blocks a concurrenct read after the update/until the commit, but you want to pessimistic locking--which means you need to lock it well before that.

My suggestions:
1. Do use transactions (whether you want such strict concurrency control or not!)

2. Do use transaction isolation level as your main control.  Technically, REPEATABLE READ or higher is reguired for your requirements. (Though the technique I describe in points 5-7 asserts similar locking and may allow you to back off to READ COMMITTED).

3. Do NOT rely on locking hints. Remember, they are just that--hints--and SS takes liberties which you may not realize.  For example, even an XLOCK hint on a SELECT is sometimes ignored (which may in part explain, why you are getting deadlock).

4. Do increase concurrency.  Keep transactions as short as the acceptable.  The ROWLOCK hint is OK (execption to #3)  because it may help and your locking strategy won't fail it is not applied.

5. Add an UpdatedTime column to the wps_part_number table. Note do not use that column as an actual lock, just as a means to lock the row. Though it can be useful (especially, along with an UpdatedBy column to diagnose concurrency problems).

6. After begin tran, but before retrieving the row from wps_part_number for edit, update UpdatedTime.  This is where you want to use ROWLOCK and is the only way to surely lock the row.

7. When the real update is performed it's sensible to refresh the UpdatedTime column, although that is not strictly necessary.

8.  Use a reasonable lock timeout (5 seconds?) on selects and an courteous error message when a timeout occurs.  This prevents long waits for locked resources.

Finally, I cannot exactly explain the deadlocks that you are experiencing. However, they certainly convince me that you are indeed locking more than one resource and they are getting locked out of order. This is no surprise, really, since SQL Server issues locks besides the ones you explicitly request and does not always issue the ones you do request. (Are we having fun yet).  The strategy I have explained, is fairly simple and failsafe way to implement pessimistic locking with SQL Server.  
 
 




 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now