Solved

locking question

Posted on 2014-01-10
3
220 Views
Last Modified: 2014-01-13
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
Comment
Question by:navajo26354
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39771647
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39771668
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39772281
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question