posted time Created time: 2017-02-09 Last updated time:

Internal Lock Types of Alinous Elastic DB

In order to keep data consistency by transaction supporting ACID, locks are essential. Mainly READ COMMITTED isolation level uses them.

Transaction Isolation Level & Lock

Most of locks are done in READ COMMITTED isolation level. SERIALIZABLE level is almost for lock free and read only transactions, because it often fails at COMMIT phase.

Explicit Locks

Explicit Locks are done by following SQL statements explicitly.

  • SELECT .. FOR UPDATE
  • SELECT .. FOR SHARE
  • LOCK TABLE

Table Lock

Table Locks are done by "LOCK TABLE" SQL statements. It locks entire table by either UPDATE or SHARE mode.

The lock remains until the transaction ends.

Row Lock

Row Locks are done by "SELECT .. FOR UPDATE" and "SELECT FOR SHARE" statements. The lock remains until the transaction ends.

Implicit Locks

When UPDATE, DELETE, and INSERT SQL Statements (DML) is executed in a transaction, and COMMIT is executed. Automatically the database system locks records. And SELECT statements use access locks.

Access Lock

Access Locks are used when SELECT statement is executed, in order to detect row locks in update mode. This locks are executed on scanning table records, and after that, they are released immediately.

DML Lock

DML Locks are used when UPDATE and DELETE SQL statements are executed in READ COMMITTED isolation level. This lock is row level lock.

By this lock, other transaction's share locks are not in contention. 

Unique Exclusive Lock

The Unique Exclusive Lock is to check the key of records are unique. This lock is row level.

This locks are used on COMMIT phase of INSERT and UPDATE SQL statements. INSERT statements use it to keep the primary key unique.

If the schema has UNIQUE constraint, it uses this locks.

When there is an another transaction already locked, it fails.



Go to Top