Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

locking question

One of our developers asked if there is a way acquire a read lock, and obtain a write lock if a certain condition is met, to update a particular row.  This will be contained in a stored procedure that will run many times throughout the day.  At times the stored procedure will run more or less at the same time another process runs the same stored procedure.  He does not want one process to update the record while another process is attempting to work on the same record.  My knowledge of locking is rather poor, so I could not answer how to accomplish this.  Any suggestions will help.
0
navajo26354
Asked:
navajo26354
1 Solution
 
PadawanDBAOperational DBACommented:
If you are updating a row, to maintain ACID compliance, SQL Server will obtain locks at escalating severity (impact) that prevent the aforementioned from occurring.  Have a read of this article, it should explain the lock modes quite nicely: http://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
0
 
Kevin CrossChief Technology OfficerCommented:
You can use a TRANSACTION within the stored procedures, allowing you to specify the ISOLATION LEVEL you want.  As mentioned above, there is lock escalation and handling; however, if you want better control of the serialization of the application, a transaction is a good way to ensure consistency.
0
 
Scott PletcherSenior DBACommented:
Begin a transaction and explicitly specify that you want the row locked while you're reading it.


BEGIN TRANSACTION

SELECT /*TOP (1)*/ ...
FROM dbo.tablename WITH ( ROWLOCK, XLOCK )
WHERE ...

IF <check_to_see_if_row_needs_modified>
BEGIN
    UPDATE dbo.tablename
    SET ... = ...
    WHERE ...
END --IF

COMMIT
END TRANSACTION
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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