 |
» |
|
|
|
|
|
5.30 How does the optimizer affect locking?
Key points
- Locks on tables are acquired according to a combination of SQL statement,
table type, and type of scan used to access the data. Remember that by
default locks must be acquired before data can be read or written.
- In Chapter 2, we saw how the optimizer locates rows
in DBEFiles. We learned that four types of scans are possible:
Locking implications of a serial scan
- When a serial scan is used, all of the rows in the table must be read to
see if they qualify for the query. An S lock at the table level is the most
efficient way of locking all rows for a read. An S lock at the table level on
a PUBLICROW or PUBLIC table usually indicates a serial scan. Remember that an
SIX lock is equivalent to an S lock plus an IX lock. An SIX lock at the table
level can also indicate that a serial scan is being used.
- When an index, hash, or TID scan is used, only some of the rows in the
table must be locked. If the default isolation level is used (isolation
levels will be discussed later in this module), then IS or IX locks at the
table level indicate that something other than a serial scan is being used to
locate rows in a PUBLICROW or PUBLIC table. If an isolation level other than
the default is used, a serial scan will also generate IS or IX locks at the
table level.
- If a serial scan is used to locate data in a PUBLICROW or PUBLIC table
and the transaction uses the default isolation level, an S lock will be
acquired at the table level during internal processing. If there is also a
need to write to a row (or page), an IX lock at the table level is also
required. Therefore, an SIX lock will also be internally obtained on the
table:
- If the need to write is known at the same time that the S lock is acquired
(such as a
DECLARE CURSOR FOR UPDATE), an SIX lock is specifically
requested instead of an S lock.
- If the need to write occurs after the S lock has been granted on the table
(such as a
SELECT without a WHERE clause, followed by an
UPDATE statement), an internal request is made to upgrade the S
lock to an SIX lock. In the case of a single transaction, S + IX = SIX, the
upgrade can only occur if no other transaction holds an S lock on the table.
If another S lock exists, the request for an SIX lock cannot be granted because
it is not compatible with the S lock. In this case, the converting transaction
will have to wait until the other transaction releases its lock.
Locking implications of an index scan
- When an index scan is used on a PUBLICROW or PUBLIC table, row (or page)
level locking is used on the table.
- The optimizer might choose an index scan instead of a serial scan, even
though all or most rows in the table will qualify for the query. This action
is taken so that sorting can be avoided. The optimizer might choose an index
scan when processing an
ORDER BY, GROUP BY, DISTINCT, or UNION clause in a
SELECT statement or when performing a sort/merge join. If all
rows in a PUBLICROW or PUBLIC table qualify, many row or page locks will be
acquired. Remember that if a query will read most or many rows in a table, it
may be useful to use the LOCK TABLE command to acquire an S lock
on the table to minimize deadlocks and shared memory needs.
Locking implications of a hash scan
- When a hash scan is used to access a row in a PUBLIC table, page level
locking is used.
Locking implications of a tid scan
- When a TID scan is used to access a row in a PUBLICROW or PUBLIC table,
row (or page) level locking is used.
Page last updated on November 29, 1995
|
|
|
Printable version
