SQL Performance Tuning [Electronic resources] نسخه متنی

Index Locks

You can lock B-tree index pages the same way that you lock table pages, but beware: index pages are "shiftier" (because data-change statements cause shifting and splitting), "big-grainier" (because typical index pages hold many small keys), and "bottleneckier" (because all index searches begin with the same page at the top node). Some special tweaks are therefore necessary. We'll tell you about them, with the warning that these considerations do not apply in all cases.

The DBMS will release index locks early. During a SELECT via an index, shared locks are set for the index and for the table pages that the matching index keys point to. In READ COMMITTED transactions, the index locks disappear after the SELECT is executed instead of after the fetch, as the following example shows.

SELECT ...
/* index locks released here */
FETCH ...
/* table locks released here */




The DBMS will split upper-level index pages before they're full. Suppose the current index has a nearly full top node pointing to a nearly empty leaf node. In this situation, the DBMS cannot travel all the way down, split a leaf node, then travel all the way back splitting upper nodes as it goesthat would cause deadlocks. So the DBMS can, and will, split top nodes "in anticipation," even when such action is unnecessary.




The DBMS will make an effort to prevent keys from shifting during a transaction. For example, Microsoft and Oracle will only mark a deleted key as "deleted" rather than actually removing the key and shifting all following keys backward. As another example, Sybase will add a pointer to an overflow page rather than actually inserting a new key and shifting all following keys forward.




The DBMS will use marks on the wall to stop concurrent transactions from passing through the same place. Consider the situation shown in Table 15-10, where Transaction #1 has placed a mark on the wall for two index keys.
































Table 15-10. Marks on the Wall in an Index

Index Leaf Page
BELGRADE
LJUBLJANA	Transaction #1 puts a mark on the wall here.
SARAJEVO
SKOPJE	Transaction #1 puts a mark on the wall here.
ZAGREB

Now, if Transaction #2 does this SELECT:

SELECT * FROM Table1
WHERE column1 BETWEEN 'BELGRADE' AND 'SARAJEVO'

it will encounter Transaction #1's mark on LJUBLJANA and wait for it to be removed. Meanwhile, if Transaction #1 actually deletes LJUBLJANA, the mark disappears, which is another reason that index keys can't be physically deleted during a transaction. This "key range locking" is an effective way to lock index keys instead of index pages. The flaw is the horrific amount of time that it takes. Key range locks occur only in transactions with isolation level SERIALIZABLE.




The DBMS will treat a lock for a strong-clustered table as if it's an index lock, not a table lock. That has to happen because a clustered table is organized as a B-tree. This constitutes a warning that the locking rules change when the table is clustered.

As for bitmap indexes, they don't present special locking problems. Updating bitmap indexes is slow because each row is indexed in multiple pages; however, the bitmap is in ROWID order so the B-tree's difficulties (shifts, big-grain locks, and bottlenecks) won't come up. That's not the case with SERIALIZABLE transactions, which are simply incompatible with bitmap indexes.

The Bottom Line: Index Locks

Index locks favor READ COMMITTED transactions.

Changes to index keys cause unexpected splits and blocks.

Putting one index on a table doesn't merely double the locking: effects are geometric.