5.31 How are indexes locked?
NOTE: the shading on this picture may not be accurate. Please click on
the picture to obtain a larger image, which is accurate.
Key points
- Locks are never acquired on a B-tree index that has been defined on a PRIVATE
or PUBLICREAD table. Concurrency control on the index is already
achieved via the table level lock that is always acquired on the table. In
other words, the S or X lock that is obtained on the table also covers the
pages in the index.
- Locks are acquired in the following way on a B-tree index that has been
defined on a PUBLICROW or PUBLIC table:
- When an index scan is performed over the table, pages in the B-tree are not
locked when they are traversed for index entries that contain matching keys.
Only rows or pages in the table itself are locked when an index is used to
read table data.
- The index leaf page that covers the row in the table is locked with an IX lock
when the index itself is modified,
regardless of the type of scan used to modify the table.
- If a row is inserted into the table, and space does not exist on the index
page that should contain an index entry for the key, then the following
occurs:
- START -- An X page lock is obtained on the index page, and it is examined to
determine whether or not compression should be performed to create free
space. If enough free space will be created, compression is performed and the
index entry is inserted onto the page.
- If compression would not result in enough free space for the index entry,
it is not attempted (the page is full and must be split into two new
pages). The data from the original page is moved to the new pages. At the end
of the split operation, each of the new pages contains half of the index data
from the original page, and the original page is freed.
- The following locks exist on the index at the end of the split operation:
- Three X page locks: one on the original leaf page, and one each on the two
newly allocated leaf pages.
- Usually three IX page locks: one on the parent (non-leaf) page of the
original page, and one each on the two neighbor (leaf) pages of the original
page (it is possible that there is only one neighbor, instead of two). The
parent and neighbor page(s) must be modified to point to the newly allocated
pages instead of to the original page. The neighbor page(s) must simply be
updated. Both an update and an insert must occur for the parent page.
There might not be enough space for the insert on the parent page, so the
process beginning with the label START: can be repeated on each
successive parent page until the root page of the index has been updated
sucessfully.
- If a row is deleted from the table, and an index page becomes empty because
the last index entry on the page was deleted, ALLBASE/SQL frees the
page. This requires an X page lock, regardless of the type of scan used to
perform the delete.
Page last updated on November 29, 1995
|
Printable version
