Lock

• Lock

• Lock lifetime

• Optimistic vs pessimisstic lock

  • Optimistic lock

  • Pessimisstic lock

    • Deadlock from pessimistic lock

      • Deadlock pattern

      • Deadlock example

• Shared vs exclusive locks

  • Shared lock

  • Exclusive lock

• Intentional shared/exclusive lock

• Row vs table locks

• Table locks

  • Add/Release Table lock

  • AUTO_INC lock

• Row vs gap vs next key locks

  • Record lock

    • Prerequistes

    • Exmaple

  • Gap lock

    • Prerequistes

    • Example

  • Next-key lock

    • Prerequistes

    • Example

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

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

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.

-- 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

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

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

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

Record lock example 1

Record lock example 2

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

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

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

Gap lock example 2

Next-key lock

Prerequistes

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

Example

• Relationship with other locks:

Interval keys

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

Next-key lock