How to prevent deadlocks in your apps
Let’s say that you have a SELECT statement that needs to return some rows. But as the SELECT statement is running, it run into a row that it needs, but that row has been previously locked, and is still locked. In most cases, the SELECT statement will have to wait until the blocking lock is released. This could be a second, a minute, or who knows how long. Let’s also assume that you don’t really care that much if the locked row is returned or not, and that you are more concerned with performance than accuracy. Or, perhaps you goal is even to prevent the same user from viewing the same data at the same time, and you don’t want any currently locked rows to be viewed by your SELECT statement.
If the above is true, consider using the READPAST locking hint. If this hint is added to a SELECT query (and it can only be used in a SELECT statement), if a row it needs is locked with a row-level lock (only, other locks are still honored), it will skip that locked record and continue on, until it is done, preventing a lock from stalling the query, providing quick results. Also, for this hint to work, the READ COMMITTED isolation level has to be in place.
While using this lock will be rare, it can come in handy in unique situations, helping to boost the performance of your application.
*****
When you normally SELECT data from a table, SQL Server will apply a shared lock on the record or records you are examining. A shared lock allows other transactions to read the same records as you, but the other ones can’t modify these records.
If you intend to UPDATE a record, SQL Server will apply what is called an update lock when it first reads the record, and then when you are ready to actually perform the UPDATE, the update lock is changed to an exclusive lock while the update occurs, and then the lock is released. When an update lock is held on a record, it does allow other transactions to see the record, but it prevents the other transaction from acquiring an exclusive lock on it. This is done in order to prevent potential deadlocks. Another transaction can only get an exclusive lock on the record once the update or exclusive lock is removed.
As you noticed, when you SELECT records, only a shared lock is used. And if you want to UPDATE a record, then an update lock is used. But what if you want to use a SELECT statement, but want to force an updated lock, not a shared lock. This is possible if you use the UPDLOCK hint. Adding the UPDLOCK hint to a SELECT statement uses an update lock instead of a shared lock. Sometime, this can come in handy when you want to read some records, but don’t want to block others from reading the same records, but you want to be able to UPDATE these records you have SELECTED knowing for sure that the data you SELECTED has not changed in the meantime. If you just SELECTED the record without the UPDLOCK hint, and then decided to later, in the same transaction to modify them, you would not be assured the records you SELECTED were not changed because another user could have changed the records.
*****
Sometimes, it is important that you get an exclusive lock until the end of your transaction. You can accomplish this using the XLOCK hint. If desired, you can combine the XLOCK hint with the PAGLOCK or the TABLOCK hints, but it cannot be used along with the NOLOCK or the UPDLOCK hints.
If you do use this hint, keep in mind that using it can reduce concurrency, which can hurt your database’s performance.
CREATE PROCEDURE spAddEmployee(
@FirstName varchar(50),
@LastName varchar(50)
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT
NULL
FROM
Employees WITH (UPDLOCK)
WHERE
FirstName = @FirstName AND
LastName = @LastName
)
BEGIN
SELECT @Result = -1
END
ELSE
BEGIN
INSERT INTO
Employees
(
FirstName,
LastName
)
VALUES
(
@FirstName,
@LastName
)
SELECT @Result = @@ERROR
END
IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result
