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.
navajo26354Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.