Solved

locking question

Posted on 2014-01-10
3
203 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
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 59

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query to Stored Procedure 6 38
In or Between 2 44
Restrict result set 1 35
Query Peformance + mulitple query plans 9 48
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 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

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now