Select For Update - Exclusive Lock

Giriş

Açıklaması şöyle

Exclusive lock, also referred to as a write lock, permits a single transaction to read and modify a record while preventing other transactions from accessing it.

An exclusive lock conflicts with other shared and exclusive lock.

If a row holds an exclusive lock, no other shared or exclusive lock can be acquired on the same row.

Select for update 3 şekilde kullanılabilir

1. Seçeneksiz : Satır üzerinde bloke olur

2. NOWAIT seçeneği : try lock gibidir. Satır üzerinde bloke olmaz

3. SKIP LOCKED seçeneği : Veriyi okurken eğer kilitli ise o satırı atlar

Açıklaması şöyle

NOWAIT: does not wait for the release of lock. If lock is held by another client and thus cannot be obtained, return immediately with message of lock conflict.

SKIP LOCKED: when reading data, skip rows whose row-level locks are held by other clients.

Örnek

Şöyle yaparız

/* session 1 acquires an xLock */
mysql> begin;

mysql> select * from user_tab where user_id=101 for update;
+---------+---------+------+-------------------+
| id      | user_id | age  | email             |
+---------+---------+------+-------------------+
| 1000076 |     101 |   10 | peter@hotmail.com |
+---------+---------+------+-------------------+

/* session 2 is blocked */
mysql> begin;

mysql> select * from user_tab where user_id=101 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction