ABOUT "ROWLOCK"
ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
Here is an example:
You need to create a stored procedure named spDeleteCategory to delete records in the database. The stored procedure must meet the following requirments:
1. Delete records in both the BlogEntry and BlogCategory tables where CategoryId equals parameter @CategoryId. 2. Avoid locking the entire table when deleting records from the BlogCategory table. 3. If an error occurs during a delete operation on either table, all changes must be rolled back, otherwise all changes should be committed.
This stored procedure can be written as follows,
CREATE PROCEDURE spDeleteCategory
(@CategoryID int)
AS
BEGIN
SET NOCOUNT ON
SET Implicit_Transactions ON
Begin try
DELETE FROM BlogEntry WHERE CategoryID = @CategoryID
DELETE FROM BlogCategoryWITH ROWLOCK WHERE CategoryID = @CategoryID
IF @@TRANSACTION > 0
COMMIT TRANSACTION;
End try
Begin catch
IF @@TRANSACTION > 0
ROLLBACK TRANSACTION;
END catch
END