Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

locking question

Posted on 2014-01-10
3
Medium Priority
?
223 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach 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.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 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