# Lock

* [Lock](#lock)
* [Lock lifetime](#lock-lifetime)
* [Optimistic vs pessimisstic lock](#optimistic-vs-pessimisstic-lock)
  * [Optimistic lock](#optimistic-lock)
  * [Pessimisstic lock](#pessimisstic-lock)
    * [Deadlock from pessimistic lock](#deadlock-from-pessimistic-lock)
      * [Deadlock pattern](#deadlock-pattern)
      * [Deadlock example](#deadlock-example)
* [Shared vs exclusive locks](#shared-vs-exclusive-locks)
  * [Shared lock](#shared-lock)
  * [Exclusive lock](#exclusive-lock)
* [Intentional shared/exclusive lock](#intentional-sharedexclusive-lock)
* [Row vs table locks](#row-vs-table-locks)
* [Table locks](#table-locks)
  * [Add/Release Table lock](#addrelease-table-lock)
  * [AUTO\_INC lock](#auto_inc-lock)
* [Row vs gap vs next key locks](#row-vs-gap-vs-next-key-locks)
  * [Record lock](#record-lock)
    * [Prerequistes](#prerequistes)
    * [Exmaple](#exmaple)
  * [Gap lock](#gap-lock)
    * [Prerequistes](#prerequistes-1)
    * [Example](#example)
  * [Next-key lock](#next-key-lock)
    * [Prerequistes](#prerequistes-2)
    * [Example](#example-1)

## Lock

* Within InnoDB, all locks are put on index.

## Lock lifetime

* Only when rollback or commit happens, the lock will be released.

## Optimistic vs pessimisstic lock

### Optimistic lock

```sql
SELECT * FROM your_tab WHERE id = 1; -- Get a = 1
-- operations
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1 AND a =1
```

### Pessimisstic lock

```sql
SELECT * FROM your_tab WHERE id = 1 FOR UPDATE; -- put lock on record 1 
-- operations
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1;
```

#### Deadlock from pessimistic lock

* SELECT ... FOR UPDATE will easily cause deadlocks.

**Deadlock pattern**

* Use optimistic lock to replace pessimistic pattern.

```sql
-- Original pessimstic pattern
Begin()  -- Begin transaction
data := SelectForUpdate(id)  -- Find existing data and compute SELECT * FROM xxx WHERE id = 1 FOR UPDATE
newData := calculate(data)   -- computation

Update(id, newData) -- Write computation result back to DB. UPDATE xxx SET data = newData WHERE id =1
Commit()


-- Transformed optimistic pattern
for {
-- Look for existing data SELECT * FROM xxx WHERE id = 1
  data := Select(id) 
  newData := calculate(data) -- computation

  -- optimistic lock: write data back to DB 
  UPDATE xxx SET data = newData WHERE id =1 AND data=oldData
  success := CAS(id, newData, data) 
  -- if successfully updated, no one modifies the data
  -- Suitable for read-intensive scenarios
  if success {
    break;
  }
}
```

**Deadlock example**

```sql
BEGIN;
SELECT * FROM biz WHERE id = ? FOR UPDATE
-- business operations
INSERT INTO biz(id, data) VALUE(?, ?);
COMMIT;
```

![](/files/08e14OBgmsS1AF0Kzury)

## Shared vs exclusive locks

### Shared lock

* Def: If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:
  * A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
  * A request by T2 for an X lock cannot be granted immediately.
* Add lock:
  1. select ...from XXX where YYY lock in share mode
  2. insert ... into select ...
* Release lock: commit / rollback

### Exclusive lock

* Def: If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.
* Add lock: Automatically by default
  1. update
  2. delete
  3. insert
  4. select ... from XXX where YYY from update
     * If there is no index on YYY, then it will lock the entire table.
* Release lock: commit / rollback

## Intentional shared/exclusive lock

* Goal: Divide the operation for adding lock into multiple phases. This is especially useful in cases of table locks.
* Operation: Automatically added by database. If a shared lock needs to be acquired, then an intentional shared lock needs to be acquired first; If an exclusive lock needs to be acquired, then an intentional exclusive lock needs to be acquired first.

## Row vs table locks

* Row lock implementation is based on index

## Table locks

* If the query does not hit any index, then only table lock could be used.

### Add/Release Table lock

* Add:
  1. Lock Table tableName READ
  2. Lock Table tableName WRITE
  3. discard table
  4. import
* Release:
  * Commit / Rollback

### AUTO\_INC lock

* Be triggered automatically when insert ... into Table xxx happens

## Row vs gap vs next key locks

* By default use next key locks except the following two cases: \*

### Record lock

#### Prerequistes

* Both needs to be met:
  * "Where condition" uses exact match (==) and the record exists.
  * "Where condition" uses primary key or unique index.

#### Exmaple

```sql
-- record lock: locked leaf node 31
select * from table where id = 31 for update
```

![Record lock example 1](/files/lIEoLl6G9o0gp9FhMZzO)

![Record lock example 2](/files/-Mk8h-FXz8-obeIrbhBN)

### Gap lock

* Gap lock only exists in repeatable read isolation level.
* Typically gap lock is open on both the "OPEN" and "CLOSE" part.

#### Prerequistes

* One of the following:
  * Where condition uses exact match (==) on a unique index and the record does not exist.
  * Where condition uses range match (>, <,>) on a unique index.
  * Where condition has index but is not unique index.

#### Example

![Gap lock example 1](/files/-Mk8h-FYLgMm6Z-6qKu2)

```sql
-- Gap key lock (12, 17)
SELECT * FROM your_tab WHERE id = 15 FOR UPDATE
```

![](/files/rdk1TwBikTnTdb4LSlL8)

```sql
-- Gap key lock (33, MAX_NUM)
SELECT * FROM your_tab WHERE id > 33 FOR UPDATE
```

![Gap lock example 2](/files/oGN9x5dgrBdzTumRYYMI)

### Next-key lock

#### Prerequistes

* If it is not covered by gap lock and record lock.

#### Example

* Relationship with other locks:

![Interval keys](/files/-Mk8h-FZKPmAy5H3Zraz)

* Next key = record lock + gap lock + record on the right border

![Next-key lock](/files/-Mk8h-F_SVirw-MiJYGI)


---

# 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://eric-zhang-seattle.gitbook.io/mess-around/traditional-db/lock.md?ask=<question>
```

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.