Database Reference
In-Depth Information
Figure 20-7. Output from sys.dm_tran_locks showing extended range locks granted to the serializable transaction
The range lock on the last possible row ( KEY = ffffffffffff ) in the clustered index, as shown in Figure 20-7 ,
will block the addition of employees with all GroupIDs greater than or equal to 10. You know that the lock is on the
last row, not because it's displayed in a visible fashion in the output of sys.dm_tran_locks but because you cleaned
out everything up to that row previously. For example, an attempt to add a new employee with GroupID = 999 will be
blocked by the PayBonus transaction.
BEGIN TRAN NewEmployee
INSERT INTO dbo.MyEmployees
VALUES (7, 999, 1000);
COMMIT
Guess what will happen if the table doesn't have an index on the GroupID column (in other words, the column in
the WHERE clause)? While you're thinking, I'll re-create the table with the clustered index on a different column.
IF (SELECT OBJECT_ID('dbo.MyEmployees')
) IS NOT NULL
DROP TABLE dbo.MyEmployees;
GO
CREATE TABLE dbo.MyEmployees
(EmployeeID INT,
GroupID INT,
Salary MONEY
);
CREATE CLUSTERED INDEX i1 ON dbo.MyEmployees (EmployeeID);
--Employee 1 in group 10
INSERT INTO dbo.MyEmployees
VALUES (1,10,1000),
--Employee 2 in group 10
(2,10,1000),
--Employees 3 & 4 in different groups
(3,20,1000),
(4,9,1000);
Search WWH ::




Custom Search