Database Reference
In-Depth Information
PRINT 'Fund balance = ' + CAST((@Fund - (@@ROWCOUNT * @Bonus)) AS VARCHAR(6)) + ' $' ;
END
COMMIT
You can observe the range locks acquired by the
PayBonus
transaction by querying
sys.dm_tran_locks
from
another connection while the
PayBonus
transaction is executing, as shown in Figure
20-6
.
Figure 20-6.
Output from sys.dm_tran_locks showing range locks granted to the serializable transaction
The output of
sys.dm_tran_locks
shows that shared-range (RangeS-S) locks are acquired on three index rows:
the first employee in
GroupID = 10
, the second employee in
GroupID = 10
, and the third employee in
GroupID = 20
.
These range locks prevent the entry of any new employee in
GroupID = 10
.
The range locks just shown introduce a few interesting side effects.
GroupID
between 10 and 20 can be added during this period. For
instance, an attempt to add a new employee with a
GroupID
of 15 will be blocked by the
PayBonus
transaction.
•
No new employee with a
BEGIN TRAN NewEmployee
INSERT INTO dbo.MyEmployees
VALUES (6, 15, 1000);
COMMIT
PayBonus
transaction turns out to be the last set in the existing data
ordered by the index, then the range lock required on the row, after the last one in the data set,
is acquired on the last possible data value in the table.
To understand this behavior, let's delete the employees with a
GroupID > 10
to make the
GroupID = 10
data set the last data set in the clustered index (or table).
•
If the data set of the
DELETE dbo.MyEmployees
WHERE GroupID > 10;
Run the updated bonus and
newemployee
again. Figure
20-7
shows the resultant output of
sys.dm_tran_locks
for
the
PayBonus
transaction.
