The amount of data locked by a statement can vary.
A 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.)
A statement can lock only a single row at a time.
This section applies only to row-level locking systems.
For TRANSACTION_READ_COMMITTED or TRANSACTION_REPEATABLE_READ isolation, Derby treats rows as cursors for SELECT statements. It 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.)
A statement can lock a range of rows (range lock).
This section applies only to 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.
| 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). |