Connections and threads

View the connection information show processlist

+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+
| 3  | root | 172.17.0.1:60542 | test | Query   | 0    | starting | show processlist |
| 5  | root | 172.17.0.1:60546 | test | Sleep   | 4168 |          | <null>           |
| 8  | root | 172.17.0.1:60552 | test | Sleep   | 4170 |          | <null>           |
+----+------+------------------+------+---------+------+----------+------------------+

mysql non enterprise version only supports one thread and one link

View the thread model show variables like 'thread'handling'

+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| thread_handling                         | one-thread-per-connection |
+-----------------------------------------+---------------------------+

[transaction submission strategy]
There are two hidden transaction submission time points to note. The first one is the autocommit variable at the level of autocommit=1 Mysql session. All the transaction submission controls in the ORM framework will be affected by this field. By default, the current statement will be automatically submitted. However, if the start transaction is displayed, it needs to be manually submitted by itself. Sometimes the ORM framework will set autocommit to 0 according to some settings or policies.

The second is that the current transaction will be implicitly committed before DDL operation. Some scripts mix DML and DDL together, so there will be consistency problems. DDL automatically commits the current transaction. Because DDL does not support transaction policy operation before 5.7. (MySQL 8.0 already supports DDL transactional)

Next key lock troubleshooting

Next key lock only occurs under RR (repeatable read) isolation level.

There are many types of lock pairs in Mysql, including table lock, record lock, gap lock, intention sharing / exclusive lock, insert intention lock, metadata lock, auto incr auto increment lock. After excluding metadata lock and auto incr auto increment lock, the remaining lock combinations are mostly used at RR isolation level.

RR isolation level is the default transaction isolation level, which is also one of Mysql's strengths. Under RR isolation level, transactions have the maximum throughput, and there is no unreal reading problem. Next key lock is to solve this problem. Simply speaking, record lock+gap lock is next key lock.

_The fundamental problem of unreal reading lies in the boundary value of records. For example, we count the number of people over 30 years old: select count (1) people where age>30 may get different result sets every time we query, because as long as the records that meet age > 30 are entered into our people table, they will be hit by query criteria.

So in order to solve the problem of unreal reading, not only the gap between records is not allowed to be inserted into records, but also the records are prevented from being modified twice, because if the two records are modified, the interval will become larger, and unreal reading will appear.

Let's take an example.

 CREATE TABLE `peoples` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_peoples_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

+----+-----+
| id | age |
+----+-----+
| 1  | 20  |
| 2  | 30  |
| 3  | 35  |
| 4  | 40  |
+----+-----+

To facilitate debugging, increase the timeout for innodb to acquire locks

show variables like '%innodb_lock_wait%'
set innodb_lock_wait_timeout=600

Open two sessions.

session A id=8:
begin
select count(1) from peoples where age>30 for update;

session B id=5:
begin
insert into peoples(age) values(31)

show processlist finds the id of the connection.

***************************[ 1. row ]***************************
Id      | 3
User    | root
Host    | 172.17.0.1:60542
db      | test
Command | Query
Time    | 0
State   | starting
Info    | show processlist
***************************[ 2. row ]***************************
Id      | 5
User    | root
Host    | 172.17.0.1:60546
db      | test
Command | Query
Time    | 394
State   | update
Info    | insert into peoples(age) values(31)
***************************[ 3. row ]***************************
Id      | 8
User    | root
Host    | 172.17.0.1:60552
db      | test
Command | Sleep
Time    | 396
State   |
Info    | <null>

• affair

Select * from information? Schema. InnoDB? TRX \ g to view transaction execution.

***************************[ 1. row ]***************************
trx_id                     | 457240
trx_state                  | LOCK WAIT
trx_started                | 2020-01-27 06:08:12
trx_requested_lock_id      | 457240:131:4:4
trx_wait_started           | 2020-01-27 06:09:25
trx_weight                 | 6
trx_mysql_thread_id        | 5
trx_query                  | insert into peoples(age) values(31)
trx_operation_state        | inserting
trx_tables_in_use          | 1
trx_tables_locked          | 1
trx_lock_structs           | 5
trx_lock_memory_bytes      | 1136
trx_rows_locked            | 4
trx_rows_modified          | 1
trx_concurrency_tickets    | 0
trx_isolation_level        | REPEATABLE READ
trx_unique_checks          | 1
trx_foreign_key_checks     | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched  | 0
trx_adaptive_hash_timeout  | 0
trx_is_read_only           | 0
trx_autocommit_non_locking | 0
***************************[ 2. row ]***************************
trx_id                     | 457239
trx_state                  | RUNNING
trx_started                | 2020-01-27 06:07:59
trx_requested_lock_id      | <null>
trx_wait_started           | <null>
trx_weight                 | 3
trx_mysql_thread_id        | 8
trx_query                  | <null>
trx_operation_state        | <null>
trx_tables_in_use          | 0
trx_tables_locked          | 1
trx_lock_structs           | 3
trx_lock_memory_bytes      | 1136
trx_rows_locked            | 5
trx_rows_modified          | 0
trx_concurrency_tickets    | 0
trx_isolation_level        | REPEATABLE READ
trx_unique_checks          | 1
trx_foreign_key_checks     | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched  | 0
trx_adaptive_hash_timeout  | 0
trx_is_read_only           | 0
trx_autocommit_non_locking | 0

457240 transaction status is LOCK WAIT waiting for lock, 457239 transaction status is RUNNING, waiting for transaction commit.

• lock

Select * from information menu schema. InnoDB menu locks \ g to view the lock usage.

***************************[ 1. row ]***************************
lock_id     | 457240:131:4:4
lock_trx_id | 457240
lock_mode   | X,GAP
lock_type   | RECORD
lock_table  | `test`.`peoples`
lock_index  | idx_peoples_age
lock_space  | 131
lock_page   | 4
lock_rec    | 4
lock_data   | 35, 7
***************************[ 2. row ]***************************
lock_id     | 457239:131:4:4
lock_trx_id | 457239
lock_mode   | X
lock_type   | RECORD
lock_table  | `test`.`peoples`
lock_index  | idx_peoples_age
lock_space  | 131
lock_page   | 4
lock_rec    | 4
lock_data   | 35, 7

According to the above transaction 457240, the status is to acquire the lock, lock_data| 35, 7, indicating the requested data. Transaction 457239 occupies the current X lock.

• Lock wait

Select * from information menu schema.innodb menu lock menu waits to view lock waiting information.

***************************[ 1. row ]***************************
requesting_trx_id | 457240
requested_lock_id | 457240:131:4:4
blocking_trx_id   | 457239
blocking_lock_id  | 457239:131:4:4

457240 transaction needs to acquire 131:4:4 lock. 457239 transaction occupies 131:4:4 lock.

• innodb monitor
  show engine innodb status

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422032240994144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 457240, ACTIVE 394 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update
insert into peoples(age) values(31)
------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000023; asc    #;;
 1: len 4; hex 00000007; asc     ;;

------------------
---TRANSACTION 457239, ACTIVE 407 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 8, OS thread handle 140556966696704, query id 104 172.17.0.1 root

MySQL thread id 5 is preparing to insert an intention lock. The intention lock is essentially a gap lock. It is to ensure the maximum concurrent insert, and unrelated row inserts are not mutually exclusive. thread id 5 needs to ensure that a gap lock is added before insertion, mainly to prevent the consistency problems caused by concurrent insertion.

session 5 and session 8 do not operate on records with id=3 and age=35, but they are locked by X+Gap Lock. Only in this way can the problem of unreal reading be solved.