воскресенье, 31 августа 2008 г.

How do indexes affect locking and blocking?

How do indexes affect locking and blocking?
// from Hands-On SQL Server – Locking and Blocking – Kalen Delaney


In general, having good indexes will help reduce blocking problems, because it will help reduce the overall time of query execution.
The faster a query can complete, and the transaction containing it is committed,
the faster any locks acquired can be released. Most serious blocking problems are due to locks being held
for a long time, so anything you can do to minimize the time that locks are held is a good thing.
A related aspect in which proper indexing can help avoid blocking problems can be observed in Hands-On
Exercise #6C. This example shows that without any index, a simple select can block on a row other than the
row it is trying to access. You might think that this wasn’t possible if SQL Server truly supports row
locking, but without an index, SQL Server has no way to know what data is in the locked row, and doesn’t
know whether the locked row is actually the one it needs to access. When we change the query to add a
nonclustered index to the table, the index gives SQL Server enough information to know that it can ignore
the locked row, and directly access the row that is indicated in the WHERE clause of the SELECT
statement.



Hands-On #6C: Use of Index Avoids Blocking
-- Use Query Analyzer for this Exercise,
-- Close all existing connections and start a new one
-- Step 1: Create and populate a small heap table
USE pubs
GO
DROP TABLE t1
GO
CREATE TABLE t1 (a int)
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 2: Update a single row
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
GO
EXEC sp_lock
GO
-- The output here should show you one X lock, on a RID;
-- that is the row that has been updated.
-- Step 2: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT should block.
-- Step 3: Return to the first query window.
-- Examine sp_lock again and note that the second process -- is waiting for a lock on the same RID that was
locked
-- in Step 2.
EXEC sp_lock
GO
ROLLBACK TRAN
GO
-- Step 4: Stay in the same query window, and change the
-- script slightly to create an index on column ‘a’
Hands-On SQL Server – Locking and Blocking – Kalen Delaney
Page 74
USE pubs
GO
DROP TABLE t1
GO
-- Create and populate the same small table
CREATE TABLE t1 ( a int)
GO
-- Build an index on the table
CREATE INDEX idx1 ON t1(a)
GO
-- Insert the same three rows into the table
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 5 : Update a single row and look at the locks
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
EXEC sp_lock
-- Now the output should show you three X locks
-- one again on a RID and two KEY locks.
-- When the key column a is updated, the leaf level of
-- the nonclustered index is adjusted.
-- Since the leaf level of the index keeps all the keys
-- in sorted order, the old key with the value 1 is moved
-- from the beginning of the leaf level to the end,
-- because now its value is 7. However, until the
-- transaction is over, a ghost entry is left in the
-- original position and the key lock is maintained.
-- So there are two key locks: one for the old value
-- and one for the new.
-- Step 6: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT will NOT block.
-- Step 7: Return to the first query window and rollback
ROLLBACK TRAN
GO

Another benefit to having indexes on a table is that there is one more option for controlling the types of
locks SQL Server will acquire. The stored procedure sp_indexoption lets you manually control the unit of
locking by letting you disallow page locks or row locks within an index. Since these options are available
only for indexes, there is no way to control the locking within the data pages of a heap. But remember that
if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption
setting for the clustered index. The index options are set for each table or index individually. Two options,
‘AllowRowLocks’ and ‘AllowPageLocks’, are both set to TRUE initially for every table and index. If both
of these options are set to FALSE for a table, only full table locks are allowed. Note that there is no option
to disallow table locks; that option must always be available.
To verify whether row or page locks have been disallowed, you can use the indexproperty function. We can
disable row locks by either setting the ‘AllowRowLocks’ option to false, or by setting the
‘DisAllowRowLocks’ option to true. There are two related options for page locks: ‘AllowPageLocks’ and
‘DisAllowPageLocks’. However, there is only one parameter we can use to check whether row locks are
allowed and one to check whether page locks are allowed.
The following command checks whether row locks have been disallowed on the authors table by supplying
the name of the clustered index on the authors table. The INDEXPROPERTY function can return 3 values:
1 means row locks are disallowed, 0 means they are allowed, and NULL means we typed something
incorrectly.
SELECT INDEXPROPERTY (object_id('authors'), 'UPKCL_auidind','IsRowLockDisallowed')
To change the value so that row locks will no longer be allowed on the authors table, we can run either of
the following two commands:
EXEC sp_indexoption 'authors.UPKCL_auidind', 'AllowRowLocks', false
EXEC sp_indexoption 'authors.UPKCL_auidind', 'DisAllowRowLocks', true

My Personal Original site - mxdev.biz

see more info about me:

http://mxdev.biz

Personal site of Max Ivak