Mysql Transactions

Shared locks vs exclusive locks

  • A shared (S) lock permits the transaction that holds the lock to read a row.
  • An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

Read lock queue and write lock queue

  • This behavior can be changed by starting the MySQL server with the --low-priority-updates flag, which will give the read lock queue a higher priority.
  • The MySQL server contains two table lock queues called the read lock queue and the write lock queue.
  • The write lock queue has priority over the read lock queue, that is, if the write lock queue is not empty, then the MySQL server will release it until it is empty, and only then proceed to the read lock queue.

Read lock queue and write lock queue

  • This behavior can be changed by starting the MySQL server with the --low-priority-updates flag, which will give the read lock queue a higher priority.
  • The MySQL server contains two table lock queues called the read lock queue and the write lock queue.
  • The write lock queue has priority over the read lock queue, that is, if the write lock queue is not empty, then the MySQL server will release it until it is empty, and only then proceed to the read lock queue.

Table_locks_immediate and table_locks_waited flags

The Table_locks_immediate variable shows the number of times a table lock request was satisfied immediately. And Table_locks_waited shows how many times it was necessary to wait for the lock to be applied. If this value is large, then you have performance problems.

It is possible to analyze the competition for blocking tables with a command 
 SHOW STATUS LIKE ‘Table%’.
Variable_name Value
Table_locks_immediate 12931
Table_locks_waited 1932

GET_LOCK function

It is possible to remove the user lock by:

  1. with the command RELEASE_LOCK(key)
  2. implicitly at session termination
  3. after the timeout
The user lock can be obtained using the function GET_LOCK(key, timeout)
 
 SELECT GET_LOCK(‘key’, 10);

GET_LOCK function

Cons:

  • It is not safe to use with SQL-expressed replication (command-by-command replication).
  • Due to the possibility of calling any number of GET_LOCK commands within one session, there is a danger of applying too many locks in the loop of your program or, for example, with the command INSERT INTO ... SELECT GET_LOCK(t1.col_name) FROM t1.
  • Such blocks are not removed when transactions are committed or rolled back.
Pros:
  •  Allows you to use one lock for all applications working with one database.
  • Allows you to implement blocking at the application level.
  • They allow to implement similar transactions in those engines in which they are absent.
  • They have lower costs.

LOCK TABLES

Cons:

  • No parallelism for table change operations.
  • Due to the monolithic write blocking, all other operations will be forced to wait for the end of the write, including the SELECT expression.
  • Modification operations must wait until the current read lock is released.
Pros:
  • They require a relatively small amount of memory.
  • Fast work when blocking a large number of tables.
  • Fast work with GROUP BY operations or with full table scanning.
  • Well suited for applications in which data rarely changes.
  • No deadlocks

row locks

Cons:

  • It involves maximum costs.
  • Lead to deadlocks.
  • They work slower than table locks if most of the table is used.
  • They work much slower for full table scanning or for GROUP BY groupings.
Pros:
  • They provide better management of competitive access.
  • Allows simultaneous execution of several changes to the same resource (if they do not conflict with each other).
  • They allow blocking one term for a long time.
  • Fewer lock conflicts when accessing different terms.

READ/WRITE lock

Features of blocking WRITE:
  • The client holding the lock can read and write to the table.
  • Other clients cannot read or change something in the tables, because the block is monolithic.
  • Requests to lock the table by other clients will be rejected while the WRITE lock is held.
  • WRITE has a LOW_PRIORITY modifier, which has no effect since version 5.6.
Features of blocking READ [LOCAL]:

  • The client holding the lock can read from tables (but not write).
  • Other clients can read from tables without explicit READ blocking.
  • Other clients can use the READ lock at the same time as you.
  • The LOCAL modifier for the MyISAM engine allows other clients to perform concurrent INSERTs while the lock is held. You can read more here
  • For InnoDB, READ LOCAL is the same as READ.

Multiple granularity locking

S - shared locks x - exclusive locks

 

If transaction T1 applied a separable lock to row r, then the request from the second transaction T2 to lock this row is processed according to the rule:​
  • T2's request for S-lock can be satisfied. Both transactions hold the lock of term r.
  • T2's request for X-lock cannot be satisfied. Transaction T2 is waiting for T1 to be released.

 

If the transaction T1 has captured a monolithic lock for row r, then the request from the second transaction T2 for any (S or X) lock of this row cannot be satisfied. Transaction T2 is waiting for T1 to be released.

 

Intention locks

S - shared locks; x - exclusive locks; IS - intention shared locks; IX - intention exclusive locks;

Mysql  lock compatibility table:

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible 
The general rule for intentional blocking is:
  • Before a transaction can acquire an S-lock on row r in table t, a transaction must first acquire an IS or stronger lock on table t.
  • Before a transaction can acquire an X lock on row r in table t, a transaction must first acquire an IX lock on table t.

Intention locks (exmaples)

# Transaction 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;
# Transaction 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE NOWAIT;

Error: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
# Transaction 1 (IS)         SELECT id, name FROM drivers FOR SHARE;

# Transaction 2 (S)          LOCK TABLES drivers READ; SELECT * FROM drivers; UNLOCK TABLES;
# Transaction 1 (IX)         SELECT id, name FROM drivers FOR UPDATE;

# Transaction 2 (S)          LOCK TABLES drivers READ; SELECT * FROM drivers; UNLOCK TABLES;
# Transaction 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;

# Transaction 2
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE SKIP LOCKED;

# Transaction 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# Transaction 2
START TRANSACTION;
UPDATE drivers SET balance = balance + 50 WHERE id > 5;
COMMIT;
# Transaction 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# ТранзаTransaction 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id > 5 FOR SHARE;
COMMIT;
# Transaction 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;
# Transaction 2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
START TRANSACTION; 
SELECT id, name FROM drivers WHERE id > 5;
COMMIT;
# In this example, rows from the blocked_drivers table are not blocked

SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers) 
FOR UPDATE;

# And in this they are blocked
SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers FOR UPDATE) 
FOR UPDATE;

Record locks

SELECT id, name 
FROM drivers 
WHERE car_id = 5
FOR UPDATE;

Select will lock the index and prevent other transactions attempting to insert/modify/delete records in the index matching the car_id = 5 condition.

Command SHOW ENGINE INNODB STATUS show 2 blocking:

RECORD LOCKS index drivers_car_id_index of table drivers trx id 2653 lock_mode X

RECORD LOCKS index PRIMARY of table locks.drivers trx id 2653 lock_mode X locks rec but not gap
The first is locking records in the secondary index, the second is in the clustered one.

NEXT KEY LOCKS

The next key lock is a combination of an index and span lock. The idea is that not only the index entry is locked, but also the gap before it.
When we talk about this type of lock, we are talking about the SERIALIZABLE isolation level, which requires no new gaps when rescanning.
Suppose we have three entries in the index (10, 20, 30), then we have four ranges of possible values ​​(-inf...10], (10...20], (20...30], ( 30…+inf), where the parenthesis means to exclude a point from the interval, and the square bracket means to include in.

Insert intention locks

The gap will be locked until a new entry is inserted into the index, this signals the intention to insert the entry in such a way that other insert operations in this gap do not conflict with each other (unless, of course, you are inserting the entry at the same position)
INSERT INTO drivers (name, car_id, balance) VALUES (‘new1’, 14, 100);
INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 17, 100);

# First transaction
START TRANSACTION;
SELECT * FROM drivers WHERE car_id > 13 FOR UPDATE;

# Second transaction
INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 15, 100);

SHOW ENGINE INNODB STATUS

RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3165 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000011; asc     ;;

1: len 4; hex 8000000b; asc     ;;

AUTO INC Locks

AUTO-INC is a table-level lock for incrementing a primary key during insertions of new records into a table. There are three primary key increment algorithms that are controlled by the innodb_autoinc_lock_mode variable. Values ​​can be 0, 1 or 2 (default in MySQL 8)

AUTO_INCREMENT lock modes

  • “INSERT-like” statements
  • “Simple inserts”
  • “Bulk inserts”
  • “Mixed-mode inserts”

 

 

 

AUTO_INCREMENT lock modes usage Implications

  • Using auto-increment with replication
  • “Lost” auto-increment values and sequence gaps
  • Specifying NULL or 0 for the AUTO_INCREMENT column
  • Assigning a negative value to the AUTO_INCREMENT column
  • Gaps in auto-increment values for “bulk inserts”
  • Auto-increment values assigned by “mixed-mode inserts”
  • Modifying AUTO_INCREMENT column values in the middle of a sequence of INSERT statements

Problems and solutions

  • Hot locks
  • Long term locks
  • Deadlocks

Problems and solutions. Hot locks

Examples:

Page visitor counter. Every time when user visit page increment views by +1. We lock table every time when user visit page. In a while pages began to load slowly, or even fall with a timeout.
authentication_log - check if user already logged today. If yes update last visit. Every time we lock table to update time.

Solutions:

  • Use faster storage like Redis.

  • For various counters where data loss is acceptable, using queuing systems to keep counting asynchronously can be a good help.

  • Use bulk insert/update when it is possible

  • Don't keep other data in the table you use for frequent locks. Ideally, you should have one row in one table

Problems and solutions. Long Time locks

Examples:

Update statistic with aggregated data from referer_tracks table. While aggregated data was not collect we could’nt resolve statistic activity update.

Solutions:

  • Exclude user interaction during blocking (collect data before create transaction with insert data into statistic activity table for example)

  • Lower the transaction isolation level (do not use lock for update if you don’t need exactly updated data from table)

  • Check that you don't have "sleeping" threads: these are connections to the database that do nothing. For example, a PHP script connected, started a transaction, and then continued, at which point your MySQL thread is in the SLEEP state. Even worse, if your code initializes a persistent connection to the database, which will be active even after the script ends.

  • Check that the queries holding the lock are using optimal indexes. The EXPLAIN command is suitable for this.

  • Try to use several short transactions instead of one long one

select DATE(referer_tracks.created_at) as date, count(*) as count 
from `referer_tracks` 
left join `referer_visitors` on `referer_visitors`.`visitor` = `referer_tracks`.`visitor` 
left join `users` on `referer_visitors`.`user_id` = `users`.`id` 
where (`users`.`organization` = 'tenantcloud' or `referer_visitors`.`visitor` is null) 
  and `referer_tracks`.`source` = 'cac' 
  and `referer_tracks`.`url` like '%utm_track=cac_profile%' 
  and `referer_tracks`.`deleted_at` is null 
group by `date` 
order by `date` asc

Problems and solutions. Deadlocks

Examples (2 transactions):
Time Session 1 Session 2
1 Start transaction
2 Start transaction
3 Updates the row with id =10 in table A. Updates the row with id =15 in table B.
4 Trying to update row with id = 15 in table B.
5 Session 1 is locked by session 2.
6 Trying to update row with id = 10 in table A.
Session 1 is locked by session 2. Session 2 is locked by session 1.
Deadlock Deadlock

Problems and solutions. Deadlocks

Examples (3 transactions):
Time Session 1 Session 2 Session 3
1 Start transaction
2 Start transaction
3 Start transaction
4 Updates the row with id =10 in table A.
5 Updates the row with id =15 in table B.
6 Updates the row with id =20 in table C.
7 Trying to update row with id = 20 in table C.
Session 1 is blocked by session 3
8 Trying to update row with id = 10 in table A.
Session 2 is blocked by session 1
9 Trying to update row with id = 15 in table B.
Session 3 is blocked by session 2
Session 1 is blocked by session 3 Session 2 is blocked by session 1 Session 3 is blocked by session 2
Deadlock Deadlock Deadlock

Problems and solutions. Deadlocks

Solutions:

InnoDB is able to detect deadlocks on its own and chooses a "victim" transaction to rollback. Keep this in mind in your applications and be prepared to retry a transaction if it rolls back due to a deadlock.

  • Determine the cause of the last deadlock with SHOW ENGINE INNODB STATUS

  • Enable the deadlock debugging flag innodb_print_all_deadlocks. When enabled, MySQL will write information about all deadlocks to the error log. Don't forget to disable the flag after debugging so as not to create unnecessary overhead

  • Minimize your transactions, keep them as short as possible. Avoid interacting with users and calling external services during a transaction.

  • For SELECT ... FOR UPDATE/SHARED transactions, consider lowering the isolation level.

  • Check if you need locks at all in queries that lock rows. It often happens that for normal SELECT operations it is enough to select data from an old snapshot without using FOR UPDATE / SHARE

  • Choose the optimal indexes for your tables so that queries scan as few index records as possible.

Debuging

# To determine which requests are currently blocked and why, you can use the following query:
SELECT 
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b 
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
	ON r.trx_id = w.requesting_engine_transaction_id;
    
# A little more detailed, but about the same result can be seen simply by running the command:
SELECT * FROM sys.innodb_lock_waits;

# Using the desired blocking_thread , get the process ID of the blocking transaction:
SELECT blocking_pid 
FROM sys.innodb_lock_waits 
WHERE blocking_trx_id = {blocking_thread}

# Using the received blocking_pid, get the thread ID:
SELECT THREAD_ID 
FROM performance_schema.threads 
WHERE PROCESSLIST_ID = {blocking_pid}

# Using the received THREAD_ID, get the text of the request:
SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID = {THREAD_ID}

# One query for all of the above:
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = (
   SELECT THREAD_ID
   FROM performance_schema.threads
   WHERE PROCESSLIST_ID = (
       SELECT blocking_pid
       FROM sys.innodb_lock_waits
       WHERE blocking_trx_id = {blocking_thread} LIMIT 1
   )
)
ORDER BY EVENT_ID;