|
Scope of locks
The amount of data locked by a statement can vary.
Table locksA statement can lock the entire table.
Table-level
locking systems always lock entire tables.
Row-level locking systems
can lock entire tables if the WHERE clause of a statement cannot use an index.
For example, UPDATES that cannot use an index lock the entire table.
Row-level
locking systems can lock entire tables if a high number of single-row locks
would be less efficient than a single table-level lock. Choosing table-level
locking instead of row-level locking for performance reasons is called lock
escalation. For more information about this topic, see "About the system's
selection of lock granularity" and "Transaction-based lock escalation" in
Tuning Derby.
Single-row locksA statement can lock only a
single row at a time.
For row-level locking systems:
- For TRANSACTION_REPEATABLE_READ isolation, the locks are released at the
end of the transaction.
- For TRANSACTION_READ_COMMITTED isolation, Derby locks
rows only as the application steps through the rows in the result. The current
row is locked. The row lock is released when the application goes to the next
row.
- For TRANSACTION_SERIALIZABLE isolation, however, Derby locks
the whole set before the application begins stepping through.
- For TRANSACTION_READ_UNCOMMITTED, no row locks are requested.
Derby locks
single rows for INSERT statements, holding each row until the transaction
is committed. If there is an index associated with the table, the previous
key is also locked.
Range locksA statement can lock a range of rows (range
lock).
For row-level locking systems:
- For any isolation level, Derby locks all
the rows in the result plus an entire range of rows for updates or deletes.
- For the TRANSACTION_SERIALIZABLE isolation level, Derby locks
all the rows in the result plus an entire range of rows in the table for SELECTs
to prevent nonrepeatable reads and phantoms.
For example, if a SELECT statement specifies rows in the Employee table
where the salary is BETWEEN two values, the system can lock more than
just the actual rows it returns in the result. It also must lock the entire range of
rows between those two values to prevent another transaction from inserting,
deleting, or updating a row within that range.
An index must be available
for a range lock. If one is not available, Derby locks
the entire table.
Table 1. Types
and scopes of locking
| Transaction Isolation Level |
Table-Level Locking |
Row-Level Locking |
| Connection.TRANSACTION_READ_UNCOMMITED (SQL: UR) |
For SELECT statements, table-level locking is never requested
using this isolation level. For other statements, same as for TRANSACTION_READ_COMMITTED. |
SELECT statements get no locks. For other statements, same
as for TRANSACTION_ READ_COMMITTED. |
| Connection.TRANSACTION_READ_COMMITTED (SQL: CS) |
SELECT statements get a shared lock on the entire table.
The locks are released when the user closes the ResultSet. Other statements
get exclusive locks on the entire table, which are released when the transaction
commits. |
SELECTs lock and release single rows as the user steps
through the ResultSet. UPDATEs and DELETEs get exclusive locks on a
range of rows. INSERT statements get exclusive locks on single rows (and sometimes
on the preceding rows). |
| Connection.TRANSACTION_REPEATABLE_READ (SQL: RS) |
Same as for TRANSACTION_SERIALIZABLE |
SELECT statements get shared locks on the rows that satisfy
the WHERE clause (but do not prevent inserts into this range). UPDATEs and
DELETEs get exclusive locks on a range of rows. INSERT statements get exclusive
locks on single rows (and sometimes on the preceding rows). |
| Connection.TRANSACTION_SERIALIZABLE (SQL: RR) |
SELECT statements get a shared lock on the entire table.
Other statements get exclusive locks on the entire table, which are released
when the transaction commits. |
SELECT statements get shared locks on a range of rows.
UPDATE and DELETE statements get exclusive locks on a range of rows. INSERT
statements get exclusive locks on single rows (and sometimes on the preceding
rows). |
|
