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.
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 are done by following SQL statements explicitly.
- SELECT .. FOR UPDATE
- SELECT .. FOR SHARE
- LOCK TABLE
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 Locks are done by "SELECT .. FOR UPDATE" and "SELECT FOR SHARE" statements. The lock remains until the transaction ends.
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 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 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.
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.