Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Which lock hints should I use (T-SQL)?

I want to implement an atomic transaction like the following:

BEGIN TRAN A

SELECT id
FROM Inventory
WITH (???)
WHERE material_id = 25 AND quantity > 10

/*
Process some things using the inventory record and
eventually write some updates that are dependent on the fact that
that specific inventory record had sufficient quantity (greater than 10).
*/

COMMIT TRAN A

The problem is that there are other transactions happening that consume quantity from our inventory, so between the time that the record is selected and the updates are written in transaction A that record could become an invalid selection because it's quantity might have been lowered below the threshold in the WHERE clause.

So the question is what locking hints should I use in the WITH clause to prevent the selected inventory record from being changed before I finish my updates and commit the transaction?

EDIT: So thanks to John, a good solution seems to be to set the transaction isolation level to REPEATABLE READ. This will will make sure that "no other transactions can modify data that has been read by the current transaction until the current transaction completes."

like image 220
Daniel Avatar asked Jan 30 '09 20:01

Daniel


People also ask

When should you use Nolock for SQL statements?

The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.

What are lock hints in SQL Server?

The ROWLOCK hint tells query optimizer to lock rows (KEY level locks) instead of taking PAGE or TABLE level locks when reading or modifying data. A row lock is the lowest level of lock possible in SQL Server.


1 Answers

You may actually be better off setting the transaction isolation level rather than using a query hint.

The following reference from Books Online provides details of each of the different Isolation levels.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Here is good article that explains the various types of locking behaviour in SQL Server and provides examples too.

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

like image 97
John Sansom Avatar answered Sep 18 '22 14:09

John Sansom