Types and Scope of Locks in Derby Systems

When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data. This lock remains in place until the transaction holding the lock issues a commit or rollback. Table-level locking lowers concurrency in a multi-user system.

When a statement reads data without making any modifications, its transaction obtains a shared lock on the data. Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released. How long this shared lock is held depends on the isolation level of the transaction holding the lock. Transactions using the TRANSACTION_READ_COMMITTED isolation level release the lock when the transaction steps through to the next row. Transactions using the TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation level hold the lock until the transaction is committed, so even a SELECT can prevent updates if a commit is never issued. Transactions using the TRANSACTION_READ_UNCOMMITTED isolation level do not request any locks.

When a user-defined update cursor (created with the FOR UPDATE clause) reads data, its transaction obtains an update lock on the data. If the user-defined update cursor updates the data, the update lock is converted to an exclusive lock. If the cursor does not update the row, when the transaction steps through to the next row, transactions using the TRANSACTION_READ_COMMITTED isolation level release the lock, and transactions using the TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation level downgrade it to a shared lock until the transaction is committed. (For update locks, the TRANSACTION_READ_UNCOMMITTED isolation level acts the same way as TRANSACTION_READ_COMMITTED.)

Update locks help minimize deadlocks.

Table 6 lists compatibility between lock types. + means compatible, - means incompatible.

Table 6. Lock Compatibility Matrix

The amount of data locked by a statement can vary.

• table locks

  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.)

• single-row locks

  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.)

• range locks

  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.
  

  Table 7. Possible 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).

In addition to the locks already described, foreign key lookups require briefly held shared locks on the referenced table (row or table, depending on the configuration).

The table and examples in this section do not take performance-based lock escalation into account. Remember that the system can choose table-level locking for performance reasons.