Lock (Concurrency Control)

Introduction

• In order to achieve data integrity and isolation between transactions, it is needed to deal with concurrency issue of accessing the same data resource

Types of lock

Shared lock

• Multiple transactions can acquire a shared lock on the same resource and read from it. No transaction is allowed to update the resource while it has a shared lock

• Shared locks are typically used when a transaction wants to read data but does not intend to modify it.

Exclusive lock

• Only one transaction can acquire an exclusive lock on a certain resource at one point in time.

• While it acquires the lock on that resource, other processes that want to acquire the lock on the same resource will have to wait. Once the lock is released, the remaining processes can acquire it and make modifications.

Level of Lock

Access Level

• Refer to the whole of table

• Here is the sql command of using access level lock

// ACCESS EXCLUSIVE TABLE LOCK
DROP TABLE
TRUNCATE
ALTER TABLE
// ACCESS SHARE TABLE LOCK
SELECT

Row Level

• Refer to the row

// ROW SHARE table lock
SELECT FOR UPDATE
SELECT FOR NO KEY SHARE
SELECT FOR SHARE
SELECT FOR KEY SHARE
// ROW EXCLUSIVE table lock
UPDATE
DELETE
INSERT

Types of locking

Optimistic locking

• Optimistic lock assumes that conflicts between users are rare and allows multiple users to access and modify a resource concurrently. When a user tries to update a resource, optimistic lock checks if the resource has been changed by another user since it was last accessed. If the resource has not been changed, the update can proceed. However, if the resource has been changed, the update is rejected and throw exception to avoid overwriting the changes made by another user.

• There are two common ways to implement optimistic locking: version number and timestamp. Version number is generally considered to be a better option

• In the application level, it will be retried until the update is success and cause rollback of transaction due to the exception thrown

• Suitable when conflicts are rare, as the performance of optimistic locking drops dramatically when concurrency is high as multiple retry will be happened due to the failure, the db usage will be much higher

Pessimistic locking

• lock the row as soon as one process attempts to modify it (or delete it) and ask the other processes to wait before doing anything.

• Suitable when conflicts are frequently happened

• Require extra resources for locking

Drawbacks

Lock contention

• A number of transactions might try to acquire locks on these resources. As subsequent transactions have to wait for others to finish, there might be a delay in executing these transactions.

Deadlock

• one transaction acquires a lock on a resource (resource A) and tries to access the lock on another resource (resource B) before releasing the lock on the first resource. At the same time, another resource acquires the lock on resource B while aiming to acquire the lock on resource A. Now both the processes are not complete without acquiring these locks.

References

Locking in Databases and Isolation MechanismsMedium

Transaction locking and row versioning guide - SQL Serverdocsmsft

Postgres Locks — A Deep DiveMedium