# 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
```sql
// ACCESS EXCLUSIVE TABLE LOCK
DROP TABLE
TRUNCATE
ALTER TABLE
// ACCESS SHARE TABLE LOCK
SELECT
```
### Row Level
* Refer to the row
```sql
// 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
{% embed url="" %}
{% embed url="" %}
{% embed url="" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://petercheng7788.gitbook.io/developer-note/backend/database/lock-concurrency-control.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.