Scope of locks

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 .

Single-row locks

A 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, 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, locks the whole set before the application begins stepping through.
For TRANSACTION_READ_UNCOMMITTED, no row locks are requested.

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

For row-level locking systems:

For any isolation level, locks all the rows in the result plus an entire range of rows for updates or deletes.
For the TRANSACTION_SERIALIZABLE isolation level, 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, locks the entire table.

The following table summarizes the types and scopes of locking.

Types and scopes of locking Transaction Isolation Level Table-Level Locking Row-Level Locking Connection.TRANSACTION_READ_UNCOMMITTED (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).