Shulou(Shulou.com)05/31 Report--

How to carry out the analysis of MySQL locking processing, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Prepare test data

Use test

CREATE TABLE T1 (id int,name varchar (20))

Alter table T1 add primary key (id)

Insert into T1 values (1), (4), (7), (10), (20), (30)

two。 Perform a test

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Combination one: id primary key + RC

-- SESSINO 1

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from T1 where id=10

Query OK, 1 row affected (0.00 sec)

-- SESSION 2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set session innodb_lock_wait_timeout=1000000

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from T1 where id=10; = = > SESSION 2 is blocked

-- SESSION 3

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 28827

Waiting_thread: 5

Wait_time: 8

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY = = > add row-level exclusive locks on the index entry of the ID=10 of the primary key

Blocking_trx_id: 28824

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 179

Blocking_query: NULL

1 row in set (0.01 sec)

Mysql > select * from information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 28827:244:3:5

Lock_trx_id: 28827

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 244

Lock_page: 3

Lock_rec: 5

Lock_data: 10 = = > add row-level exclusive locks on the index entries of the ID=10 of the primary key

* 2. Row * *

Lock_id: 28824:244:3:5

Lock_trx_id: 28824

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 244

Lock_page: 3

Lock_rec: 5

Lock_data: 10 = = > add row-level exclusive locks on the index entries of the ID=10 of the primary key

2 rows in set (0.00 sec)

-- SESSION 1 rolls back transactions

Mysql > rollback

Query OK, 0 rows affected (0.01 sec)

-- SESION 2 rollback transactions

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

Combination two: id unique index + RC

In this combination, id is not the primary key, but the secondary index key value of a Unique. So what locks do you need to add under the RC isolation level, delete from T1 where id = 10;?

-- prepare test data

Use test

Drop table t1

CREATE TABLE T1 (id int,name varchar (20))

Alter table T1 add primary key (name)

CREATE UNIQUE INDEX idx_id on T1 (id)

Insert into T1 values (1), (2)), (3)), (5)), (6)), (10)

Commit

-- SESSION 1

Mysql > delete from T1 where id=10

Query OK, 1 row affected (0.00 sec)

-- SESSION 2

Mysql > delete from T1 where id=10; = = > blocked

-- SESSION 3

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 28929

Waiting_thread: 5

Wait_time: 10

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 28928

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 13

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 28929:248:4:7

Lock_trx_id: 28929

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 7

Lock_data: 10

* 2. Row * *

Lock_id: 28928:248:4:7

Lock_trx_id: 28928

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 7

Lock_data: 10

2 rows in set (0.00 sec)

There is no information about locking records related to the primary key in the information queried above. Next we use where name='a' to delete records in SESSION 4 (10)

-- SESSION 4

Mysql > set session innodb_lock_wait_timeout=1000000

Query OK, 0 rows affected (0.00 sec)

Mysql > set session tx_isolation='read-committed'

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from T1 where name='d'; = = > blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 28929

Waiting_thread: 5 = > MSYQL thread of SESSION 2 ID is blocked at 5

Wait_time: 339

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id = = > the lock is added to the idx_id index

Blocking_trx_id: 28928

Blocking_thread: 1 = > MYSQL thread of SESSION 1 ID holds lock resources for 1 causing blocking

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 342

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 28931

Waiting_thread: 7 = > MSYQL thread of SESSION 4 ID is blocked at 7

Wait_time: 27

Waiting_query: delete from t1 where name='d'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY = = > the lock is added to the primary key

Blocking_trx_id: 28928

Blocking_thread: 1 = > MYSQL thread of SESSION 1 ID holds lock resources for 1 causing blocking

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 342

Blocking_query: NULL

2 rows in set (0.01sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 28931:248:3:7

Lock_trx_id: 28931

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'d' = = > add a row-level exclusive lock on an index item whose key value of the primary key is D

* 2. Row * *

Lock_id: 28928:248:3:7

Lock_trx_id: 28928

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'d'

* 3. Row * *

Lock_id: 28929:248:4:7

Lock_trx_id: 28929

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 7

Lock_data: 10

* * 4. Row *

Lock_id: 28928:248:4:7

Lock_trx_id: 28928

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 7

Lock_data: 10 = = > add row-level exclusive locks on records (index entries) of the ID=10 of the unique index idx_id

4 rows in set (0.00 sec)

-- deadlock found in SESSION 2 (this issue will be studied later)

Mysql > delete from T1 where id=10

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- SESSION 1, 2, 4 rollback transactions

In the test of this combination, SESSION 1 blocked both SESSION 2 and SESSION 4, thus proving that when deleting the record of ID=10

Locks row-level exclusive locks are added on both the unique index and the primary key index.

Combination 3: id column is a secondary non-unique index, RC isolation level

-- prepare test data

Drop INDEX idx_id on t1

Delete from t1

CREATE INDEX idx_id on T1 (id)

Insert into T1 values (2), (6)), (10)), (10), (11)), (15)

Mysql > select * from test.t1

+-+ +

| | id | name |

+-+ +

| | 2 | zz |

| | 6 | c |

| | 10 | b |

| | 10 | d | |

| | 11 | f | |

| | 15 | a |

+-+ +

6 rows in set (0.00 sec)

-- SESSION 1 deletes the record of ID=10

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Mysql > select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

Set autocommit=0

Delete from t1 where id=10

-- SESSION 2 deletes data from ID=10

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Delete from T1 where id=10; = = > blocked

-- View lock information in SESSON 5

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 29501

Waiting_thread: 2 = = > SESSION 2

Wait_time: 38

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 29496

Blocking_thread: 1 = = > SESSION 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 83

Blocking_query: NULL

1 row in set (0.11 sec)

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 29501 ID 248 ID 4 = > lock ID of session 2

Lock_trx_id: 29501

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

* 2. Row * *

Lock_id: 29496 ID 248 ID 4 = > lock ID of session 1

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

2 rows in set (0.00 sec)

Mysql > show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 1 | root | localhost | test | Sleep | 995 | | NULL |

| | 2 | root | localhost | test | Query | 950 | updating | delete from T1 where id=10 |

| | 3 | root | localhost | NULL | Sleep | 1772 | | NULL |

| | 4 | root | localhost | NULL | Sleep | 836 | | NULL |

| | 5 | root | localhost | test | Query | 0 | init | show full processlist | |

+-+-

5 rows in set (0.00 sec)

From the lock information above, we can see that although there are 2 items of ID=10 data (10rem b) and (10m d), SESSION 2 can only record (10m b).

It is superior to be locked, which also shows that the row lock of MYSQL is acquired one by one, and the lock acquisition on (10) d is needed to delete successfully.

After the operation, acquire the lock on (10m d).

-- SESSION 3 deletes records with name = 5

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where name='b'; = = > blocked

-- View lock information in SESSON 5

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 29501

Waiting_thread: 2

Wait_time: 2897

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 29496

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 2942

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 29503

Waiting_thread: 3

Wait_time: 116

Waiting_query: delete from t1 where name='b'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 29496

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 2942

Blocking_query: NULL

2 rows in set (0.01sec)

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 29503 ID 248 ID for SESSION 3

Lock_trx_id: 29503

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b' = = > SESSION 3's lock on waiting for an index entry with a key value of b for the primary key

* 2. Row * *

Lock_id: 29496 ID 248 ID 3 ID for SESSION 1

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b' = = > SESSION 1 holds a lock on an index entry with a primary key index value of b

* 3. Row * *

Lock_id: 29501:248:4:4

Lock_trx_id: 29501

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

* * 4. Row *

Lock_id: 29496:248:4:4

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

4 rows in set (0.00 sec)

SHOW ENGINE innodb status\ G

-

TRANSACTIONS

-

Trx id counter 29504

Purge done for trx's n:o

< 29501 undo n:o < 0 state: running but idle History list length 755 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 5, OS thread handle 0x7f8e1ffff700, query id 74 localhost root init SHOW ENGINE innodb status ---TRANSACTION 29502, not started MySQL thread id 4, OS thread handle 0x7f8e54c43700, query id 51 localhost root cleaning up ---TRANSACTION 29503, ACTIVE 1137 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 3, OS thread handle 0x7f8e54c84700, query id 70 localhost root updating delete from t1 where name='b' ------- TRX HAS BEEN WAITING 1137 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 248 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29503 lock_mode X locks rec but not gap waiting ==>

SESSION 3 has exclusive row locks waiting on the primary key, but no gap locks

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32

0: len 1; hex 62; asc b

1: len 6; hex 000000007338; asc S8

2: len 7; hex 240000015221ce; asc $R!

3: len 4; hex 8000000a; asc

-

-TRANSACTION 29501, ACTIVE 3918 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s)

MySQL thread id 2, OS thread handle 0x7f8e54cc5700, query id 48 localhost root updating

Delete from t1 where id=10

-TRX HAS BEEN WAITING 3918 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 248page no 4 n bits 80 index `idx_ id` of table `test`.`t1` trx id 29501 lock_mode X locks rec but not gap waiting

= = > SESSION 2 has exclusive row locks waiting on non-unique index idx_id, but no gap lock waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000a; asc

1: len 1; hex 62; asc b

-

-TRANSACTION 29496, ACTIVE 3963 sec

3 lock struct (s), heap size 360,4 row lock (s), undo log entries 2

MySQL thread id 1, OS thread handle 0x7f8e54d06700, query id 37 localhost root cleaning up

-- SESSION 4 deletes the record of name='d'

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where name='d'; = = > blocked

* * 1. Row *

Waiting_trx_id: 29501

Waiting_thread: 2

Wait_time: 4865

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 29496

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 4910

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 29503

Waiting_thread: 3

Wait_time: 2084

Waiting_query: delete from t1 where name='b'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 29496

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 4910

Blocking_query: NULL

* 3. Row * *

Waiting_trx_id: 29504

Waiting_thread: 4 = > SESSION 4

Wait_time: 24

Waiting_query: delete from t1 where name='d'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 29496

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 4910

Blocking_query: NULL

3 rows in set (0.00 sec)

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 29504 ID 248 3 ID for SESSION 4

Lock_trx_id: 29504

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 5

Lock_data:'d' = = > SESSION 4's lock on waiting for an index entry with a primary key index key value of d

* 2. Row * *

Lock_id: 29496 ID 248 ID 3 ID for SESSION 1

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 5

Lock_data:'d' = > SESSION 1 holds a lock on an index entry with a key value of d for the primary key

* 3. Row * *

Lock_id: 29503:248:3:4

Lock_trx_id: 29503

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* * 4. Row *

Lock_id: 29496:248:3:4

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* * 5. Row * *

Lock_id: 29501:248:4:4

Lock_trx_id: 29501

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

* 6. Row * *

Lock_id: 29496:248:4:4

Lock_trx_id: 29496

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 4

Lock_data: 10,'b'

6 rows in set (0.00 sec)

SHOW ENGINE innodb status\ G

-

TRANSACTIONS

-

Trx id counter 29505

Purge done for trx's n:o

< 29501 undo n:o < 0 state: running but idle History list length 755 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 5, OS thread handle 0x7f8e1ffff700, query id 89 localhost root init SHOW ENGINE innodb status ---TRANSACTION 29504, ACTIVE 736 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 4, OS thread handle 0x7f8e54c43700, query id 86 localhost root updating delete from t1 where name='d' ------- TRX HAS BEEN WAITING 736 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 248（行锁所在表空间是248） page no 3（数据页是3） n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29504 lock_mode X locks rec but not gap waiting ==>

SESSION 4 waits for exclusive row locks on the primary key, but does not wait for gap locks

Record lock, heap no 5 PHYSICAL RECORD (data row 5; taken together to mean the fifth row of the third data page locked in tablespace 248): n_fields 4; compact format; info bits 32

0: len 1; hex 64; asc d

1: len 6; hex 000000007338; asc S8

2: len 7; hex 240000015221f1; asc $R!

3: len 4; hex 8000000a; asc

-

-TRANSACTION 29503, ACTIVE 2796 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s)

MySQL thread id 3, OS thread handle 0x7f8e54c84700, query id 70 localhost root updating

Delete from t1 where name='b'

-TRX HAS BEEN WAITING 2796 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 248page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29503 lock_mode X locks rec but not gap waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32

0: len 1; hex 62; asc b

1: len 6; hex 000000007338; asc S8

2: len 7; hex 240000015221ce; asc $R!

3: len 4; hex 8000000a; asc

-

-TRANSACTION 29501, ACTIVE 5577 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s)

MySQL thread id 2, OS thread handle 0x7f8e54cc5700, query id 48 localhost root updating

Delete from t1 where id=10

-TRX HAS BEEN WAITING 5577 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 248page no 4 n bits 80 index `idx_ id` of table `test`.`t1` trx id 29501 lock_mode X locks rec but not gap waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000a; asc

1: len 1; hex 62; asc b

-

-TRANSACTION 29496, ACTIVE 5622 sec

3 lock struct (s), heap size 360,4 row lock (s), undo log entries 2

MySQL thread id 1, OS thread handle 0x7f8e54d06700, query id 37 localhost root cleaning up

Conclusion:

From the above tests, we can infer that the delete from T1 where id=10 executed by SESSION 1 will be in the non-unique index idx_id

Row-level exclusive locks are added to two index items with key values of 10 on the, and row-level exclusivity is added on two index items with key values b and d on the primary key

Lock.

Combination 4: id No Index + RC

-- Delete idx_id index

Use test

DROP INDEX idx_id on t1

Mysql > show index from T1\ G

* * 1. Row *

Table: t1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

-- SESSION 1 deletes the record of ID=10

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Use test

Delete from t1 where id=10

-- SESSION 2 deletes the record of ID=10

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where id=10; = = > blocked

-- SESSION 5 to view lock information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30476

Waiting_thread: 2

Wait_time: 15

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30471

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 39

Blocking_query: NULL

1 row in set (0.11 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30476:248:3:4

Lock_trx_id: 30476

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* 2. Row * *

Lock_id: 30471:248:3:4

Lock_trx_id: 30471

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

2 rows in set (0.00 sec)

-- SESSION 3 deletes the record of ID=15

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where id=15; = = > blocked

-- SESSION 4 deletes the record of id=2

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where id=2; = = > blocked

-- SESSION 6 deletes name='zz'

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where name='zz'; = = > will not be blocked

-- SESSION 7 deletes the record of name='a' (15recovera)

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Use test

Delete from T1 where name='a'; = = > blocked

-- SESSION 5 to view lock information

SELECT CONCAT ('thread', b.trxboxes mysqlthread reading AS who_blocks' from', p.host)

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

MAX (TIMESTAMPDIFF (SECOND,r.trx_wait_started,NOW () AS max_wait_time

COUNT (*) AS num_waiters

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trxroomid` = w.`notify _ trx_ id`

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.`trx _ id` = w.`requesting _ trx_ id`

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id = b.`trx _ mysql_thread_ id`

GROUP BY who_blocks ORDER BY num_waiters DESC\ G

* * 1. Row *

Who_blocks: thread 3 from localhost

Idle_in_trx: 0

Max_wait_time: 363

Num_waiters: 2 = = > in the next SQL query, you can see that SESSION 3 blocks SESSION 4 and SESSION 7

* 2. Row * *

Who_blocks: thread 1 from localhost

Idle_in_trx: 554

Max_wait_time: 530

Num_waiters: 2 = = > in the next SQL query, you can see that SESSION 1 blocks SESSION 2 and SESSION 3

* 3. Row * *

Who_blocks: thread 4 from localhost

Idle_in_trx: 0

Max_wait_time: 202

Num_waiters: 1 = = > in the next SQL query, you can see that SESSION 4 blocks SESSION 7

* * 4. Row *

Who_blocks: thread 2 from localhost

Idle_in_trx: 0

Max_wait_time: 384

Num_waiters: 1 = = > in the next SQL query, you can see that SESSION 2 blocks SESSION 3

4 rows in set (0.00 sec)

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30476

Waiting_thread: 2

Wait_time: 374

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30471

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 398

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 30477

Waiting_thread: 3

Wait_time: 228

Waiting_query: delete from t1 where id=15

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30471

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 398

Blocking_query: NULL

* 3. Row * *

Waiting_trx_id: 30477

Waiting_thread: 3

Wait_time: 228

Waiting_query: delete from t1 where id=15

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30476

Blocking_thread: 2

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=10

* * 4. Row *

Waiting_trx_id: 30478

Waiting_thread: 4

Wait_time: 207

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

* * 5. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 46

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30478

Blocking_thread: 4

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

* 6. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 46

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

6 rows in set (0.00 sec)

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30485:248:3:7

Lock_trx_id: 30485

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 2. Row * *

Lock_id: 30478:248:3:7

Lock_trx_id: 30478

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 3. Row * *

Lock_id: 30477:248:3:7

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* * 4. Row *

Lock_id: 30477:248:3:4

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* * 5. Row * *

Lock_id: 30476:248:3:4

Lock_trx_id: 30476

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* 6. Row * *

Lock_id: 30471:248:3:4

Lock_trx_id: 30471

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

6 rows in set (0.00 sec)

SQL statements executed by each SESSION:

SESSION 1:delete from T1 where id=10; = = > executed successfully

SESSION 2:delete from t1 where id=10

SESSION 3:delete from t1 where id=15

SESSION 4:delete from t1 where id=2

SESSION 6: delete from T1 where name='zz'; = = > executed successfully

SESSION 7: delete from t1 where name='a'

Table data:

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 15 | a |

| | 10 | b |

| | 6 | c |

| | 10 | d | |

| | 11 | f | |

| | 2 | zz |

+-+ +

6 rows in set (0.00 sec)

SESSION 1:delete from T1 where id=10; = = > executed successfully

* * 1. Row *

Waiting_trx_id: 30476

Waiting_thread: 2

Wait_time: 374

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30471

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 398

Blocking_query: NULL

* 6. Row * *

Lock_id: 30471:248:3:4

Lock_trx_id: 30471

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

6 rows in set (0.00 sec)

Delete from T1 where id=10 locks the entire primary key but only blocks where id=.. (the ID field is used in the WHERE condition)

Statements with where name= values will not block as long as they do not need to lock the primary key index entry of the name='a', as the following experiments will illustrate.

SESSION 3 is blocked on lines with primary key values an and b (blocked on two primary key values).

* 2. Row * *

Waiting_trx_id: 30477 = > transaction ID for SESSION 3

Waiting_thread: 3

Wait_time: 228

Waiting_query: delete from t1 where id=15

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30471

Blocking_thread: 1 = = > SESSIO 1 blocking SESSION 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 398

Blocking_query: NULL

* 3. Row * *

Lock_id: 30477 ID 248 ID 3 ID 7 = > lock 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'= > SESSION 3 is blocked in a row with a key value of the primary key index

* * 4. Row *

Lock_id: 30477 ID 248 ID 3 ID of SESSION 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 4

Lock_data:'b' = = > SESSION 3 is blocked in a row with a key value of b in the primary key index

SESSION 4 Lock Information:

* * 4. Row *

Waiting_trx_id: 30478

Waiting_thread: 4

Wait_time: 207

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

SESSION 4 is blocked by SESSION 3.

* 2. Row * *

Lock_id: 30478:248:3:7

Lock_trx_id: 30478

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'= > SESSION 4 is blocked in a row with a key value of the primary key index

The execution of delete from T1 where name='zz' by SESSION 6 will not be blocked, which proves that SESSION 1 delete from T1 where id=10 locks the entire primary key but only blocks

Where id=.. (the ID field is used in the WHERE condition). Statements with where name= values will not block as long as they do not need to lock the primary key index entry of name='a'.

SHOW ENGINE innodb status\ G

SESSION 7 execution of delete from T1 where name='a' is blocked. Here is the lock information for SESSION 7.

* * 5. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 46

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30478

Blocking_thread: 4

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

* 6. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 46

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

6 rows in set (0.00 sec)

You can see from the information above that SESSION 7 is blocked by SESSION 3 and SESSION 4.

* * 1. Row *

Lock_id: 30485:248:3:7

Lock_trx_id: 30485

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

SESSION 7 is blocked in rows with a key value of the primary key index

=

For transactions that commit SESSION 1 now, SESSION 4 should be blocked by SESSION 6.

SESSION 1 commit transaction:

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

At this time, it was found that SESSION 2, 3, 4, and 7 were still blocked.

SELECT CONCAT ('thread', b.trxboxes mysqlthread reading AS who_blocks' from', p.host)

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

MAX (TIMESTAMPDIFF (SECOND,r.trx_wait_started,NOW () AS max_wait_time

COUNT (*) AS num_waiters

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trxroomid` = w.`notify _ trx_ id`

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.`trx _ id` = w.`requesting _ trx_ id`

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id = b.`trx _ mysql_thread_ id`

GROUP BY who_blocks ORDER BY num_waiters DESC\ G

-> GROUP BY who_blocks ORDER BY num_waiters DESC\ G

* * 1. Row *

Who_blocks: thread 6 from localhost = = > SESSION 6 blocked 2 SESSION

Idle_in_trx: 5288

Max_wait_time: 74

Num_waiters: 2

* 2. Row * *

Who_blocks: thread 3 from localhost = = > SESSION 3 blocked 2 SESSION

Idle_in_trx: 0

Max_wait_time: 5385

Num_waiters: 2

* 3. Row * *

Who_blocks: thread 4 from localhost = > SESSION 4 blocked 1 SESSION

Idle_in_trx: 0

Max_wait_time: 5224

Num_waiters: 1

* * 4. Row *

Who_blocks: thread 2 from localhost = > SESSION 2 blocked 1 SESSION

Idle_in_trx: 0

Max_wait_time: 74

Num_waiters: 1

4 rows in set (0.00 sec)

Check which two SESSION are blocked by SESSION 6

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

...

Blocking_query: delete from t1 where id=2

* * 4. Row *

Waiting_trx_id: 30476

Waiting_thread: 2

Wait_time: 221

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30480

Blocking_thread: 6

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 5435

Blocking_query: NULL

...

* 6. Row * *

Waiting_trx_id: 30477

Waiting_thread: 3

Wait_time: 221

Waiting_query: delete from t1 where id=15

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30480

Blocking_thread: 6

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 5435

Blocking_query: NULL

6 rows in set (0.00 sec)

We see SESSION 6 blocking SESSION 3 and SESION 2.

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

...

* * 4. Row *

Lock_id: 30477:248:3:2

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* * 5. Row * *

Lock_id: 30476:248:3:2

Lock_trx_id: 30476

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* 6. Row * *

Lock_id: 30480:248:3:2

Lock_trx_id: 30480

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

6 rows in set (0.00 sec)

SESSION 6 executes delete from T1 where name='zz' to lock records with primary key ZZ, while SESSION 2 executes delete from T1 where id=10

And SESSION 3 delete from T1 where id=15 are blocked by SESION 6 because the ID column has no index and needs to lock the entire primary key.

SESSION 4 is blocked by SESSON 3

ELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30478

Waiting_thread: 4

Wait_time: 5532

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

...

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30485:248:3:7

Lock_trx_id: 30485

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 2. Row * *

Lock_id: 30478 ID 248 3 ID 7 = > lock of SESSION 4

Lock_trx_id: 30478

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 3. Row * *

Lock_id: 30477 ID 248 ID 3 ID 7 = > lock 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* * 4. Row *

Lock_id: 30477 ID 248 ID 3 ID for SESSION 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* * 5. Row * *

Lock_id: 30476 ID 248 ID 3 ID for SESSION 2

Lock_trx_id: 30476

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* 6. Row * *

Lock_id: 30480 ID 248 ID 3 ID for SESSION 6

Lock_trx_id: 30480

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

6 rows in set (0.00 sec)

SESSION 6 blocks SESSION 2 and SESSION 3, SESSION 2 blocks SESSION 3, and SESSION 3 blocks SESSION 4. Commit SESSION 6 transactions in this case

The SESSION 2 SQL can execute successfully, but SESSION 3, SESION 4, and SESSION 7 are still blocked.

SESSION 7 is blocked by SESSION 3 and SESSION 4

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

...

* 2. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 5371

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=15

* 3. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 5371

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30478

Blocking_thread: 4

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

..

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30485 ID 248 3 ID 7 = > lock ID of SESSION 7

Lock_trx_id: 30485

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 2. Row * *

Lock_id: 30478 ID 248 3 ID 7 = > lock of SESSION 4

Lock_trx_id: 30478

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 3. Row * *

Lock_id: 30477 ID 248 ID 3 ID 7 = > lock 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* * 4. Row *

Lock_id: 30477 ID 248 ID 3 ID for SESSION 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* * 5. Row * *

Lock_id: 30476 ID 248 ID 3 ID for SESSION 2

Lock_trx_id: 30476

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

* 6. Row * *

Lock_id: 30480 ID 248 ID 3 ID for SESSION 6

Lock_trx_id: 30480

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data: 'zz'

6 rows in set (0.00 sec)

SESSION 3 executes delete from T1 where id=15 needs to lock the entire primary key, SESSION 4 executes delete from T1 where id=2

Need to lock the entire primary key, SESSION 7 delete from T1 where name='a' needs to lock the index entry with the primary key value a, so it is SESION

3 and SESSION 4 blocking.

SESSION 6 blocks SESSION 2 and SESSION 3, SESSION 2 blocks SESSION 3, and SESSION 3 blocks SESSION 4. Commit SESSION 6 transactions in this case

The SESSION 2 SQL can execute successfully, but SESSION 3, SESION 4, and SESSION 7 are still blocked.

SESSION 6 commit transaction:

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

SESSION 2:

Mysql > delete from T1 where id=10

Query OK, 0 rows affected (2 hours 4 min 18.26 sec)

SESSION 3:

Mysql > delete from T1 where id=15

Query OK, 1 row affected (2 hours 1 min 52.24 sec)

We see that both SESION 2 and SESSION 3 SQL are executed successfully, which is inconsistent with the expected successful execution of SESSION 2 SQL and SESSION 3 blocked by SESSION 2.

The reason is that the data of id=10 has been deleted by SESSION 1, and SESSION 2 has no data to delete, so it does not need to be locked, so SESSION 3 is not SESION.

(2) obstruction.

The current situation is that SESSION 4 and SESSION 7 are blocked after SESSION 6 commits the transaction.

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30478

Waiting_thread: 4

Wait_time: 7682

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 7703

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 7521

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30477

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 7703

Blocking_query: NULL

* 3. Row * *

Waiting_trx_id: 30485

Waiting_thread: 7

Wait_time: 7521

Waiting_query: delete from t1 where name='a'

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30478

Blocking_thread: 4

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

3 rows in set (0.00 sec)

We see that SESSION 3 blocks SESSION 4 and SESSION 7, and SESSION 4 blocks SESSION 7. (it's just an illusion that SESSION 4 is not blocking.

SESSION 7, because SESSION 4 executes delete from T1 where id=2 locks the primary key but only blocks the use of the ID field in the WHERE sentence

The SQL that does not block SQL,SESSSION 7 that does not use the ID field in the WHERE sentence and overwrites a different record from SESSION 4 is Selete from

T1 where name='a' does not use the ID field in the WHERE sentence).

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30485 ID 248 3 ID 7 = > lock ID of SESSION 7

Lock_trx_id: 30485

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 2. Row * *

Lock_id: 30478 ID 248 3 ID 7 = > lock of SESSION 4

Lock_trx_id: 30478

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

* 3. Row * *

Lock_id: 30477 ID 248 ID 3 ID 7 = > lock 3

Lock_trx_id: 30477

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 7

Lock_data:'a'

3 rows in set (0.00 sec)

We see that SESSION needs to lock records with a primary key value on 3-4-7.

SESSION 4 needs to lock the entire primary key to execute delete from T1 where id=2.

SESSION 7 executes delete from T1 where name='a', because NAME is a primary key column and only needs to lock records with a primary key value.

SQL statements executed by each SESSION:

SESSION 3:delete from T1 where id=15; = = > executed successfully, but the transaction has not been committed yet

SESSION 4:delete from t1 where id=2

SESSION 7: delete from t1 where name='a'

=

After submitting SESSION 3, the SQL of SESSION 4 and SESSION 7 was executed successfully.

SESSION commits the transaction on 2-4-7.

Question: if SESSON 1 executes delete from T1 where id=15 to check lock information, it will show that it is blocked by SESSION 1.

A: no, the following tests can prove it.

Delete from t1

Insert into T1 values (1), (2)), (3), (3), (15), (6), (10)

Commit

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 15 | a |

| | 3 | b | |

| | 6 | c |

| | 10 | d | |

| | 1 | f | |

| | 2 | zz |

+-+ +

6 rows in set (0.00 sec)

SESSION 1:

Delete from t1 where id=15

SESSION 2:

Delete from T1 where id=2; = = > blocked

SESSION 3:

Delete from T1 where id=10; = = > blocked

SESSION 4:

Delete from T1 where id=1; = = > blocked

SELECT CONCAT ('thread', b.trxboxes mysqlthread reading AS who_blocks' from', p.host)

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

MAX (TIMESTAMPDIFF (SECOND,r.trx_wait_started,NOW () AS max_wait_time

COUNT (*) AS num_waiters

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trxroomid` = w.`notify _ trx_ id`

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.`trx _ id` = w.`requesting _ trx_ id`

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id = b.`trx _ mysql_thread_ id`

GROUP BY who_blocks ORDER BY num_waiters DESC\ G

* * 1. Row *

Who_blocks: thread 1 from localhost

Idle_in_trx: 153

Max_wait_time: 80

Num_waiters: 3

* 2. Row * *

Who_blocks: thread 2 from localhost

Idle_in_trx: 0

Max_wait_time: 46

Num_waiters: 2

* 3. Row * *

Who_blocks: thread 3 from localhost

Idle_in_trx: 0

Max_wait_time: 11

Num_waiters: 1

3 rows in set (0.00 sec)

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30516

Waiting_thread: 2

Wait_time: 113

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30514

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 186

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 30517

Waiting_thread: 3

Wait_time: 79

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30514

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 186

Blocking_query: NULL

* 3. Row * *

Waiting_trx_id: 30517

Waiting_thread: 3

Wait_time: 79

Waiting_query: delete from t1 where id=10

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30516

Blocking_thread: 2

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

* * 4. Row *

Waiting_trx_id: 30518

Waiting_thread: 4

Wait_time: 44

Waiting_query: delete from t1 where id=1

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30514

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 186

Blocking_query: NULL

* * 5. Row * *

Waiting_trx_id: 30518

Waiting_thread: 4

Wait_time: 44

Waiting_query: delete from t1 where id=1

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30516

Blocking_thread: 2

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=2

* 6. Row * *

Waiting_trx_id: 30518

Waiting_thread: 4

Wait_time: 44

Waiting_query: delete from t1 where id=1

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30517

Blocking_thread: 3

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 0

Blocking_query: delete from t1 where id=10

6 rows in set (0.01 sec)

Combination four-2:id and ID2 indexless + RC

CREATE TABLE T2 (id int,id2 int,name varchar (20))

Alter table T2 add primary key (name)

Insert into T2 values (1 recorder 31), (2 recorder 32), (3), (3), (3), (15), (45)), (6), (36)), (10), (40).

Commit

SESSION 1:

Delete from t2 where id=15

SESSION 2:

Delete from T2 where id2=32; = = > blocked

SESSION 5 view lock information:

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30564

Waiting_thread: 2

Wait_time: 38

Waiting_query: delete from t2 where id2=32

Waiting_table_lock: `test`.`t2`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30562

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 76

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30564 ID 250 3 ID 5 = > ID 2 lock

Lock_trx_id: 30564

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 5

Lock_data:'a'

* 2. Row * *

Lock_id: 30562 ID 250 3 ID 5 = > ID 1 lock

Lock_trx_id: 30562

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 5

Lock_data:'a'

2 rows in set (0.00 sec)

SESSION 1 and SESSION 2 roll back transactions

SESSION 1:

Delete from T2 where id in (2, 10, 10, 3)

SESSION 2:

Delete from T2 where id2 in (45pc36pm 31); = = > blocked

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30576

Waiting_thread: 2

Wait_time: 232

Waiting_query: delete from T2 where id2 in (45, 6, 36, 31)

Waiting_table_lock: `test`.`t2`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30571

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 241

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30576:250:3:4

Lock_trx_id: 30576

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

* 2. Row * *

Lock_id: 30571:250:3:4

Lock_trx_id: 30571

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 4

Lock_data:'b'

2 rows in set (0.00 sec)

Mysql > select * from T2

+-+

| | id | id2 | name | |

+-+

| | 15 | 45 | a |

| | 3 | 33 | b | |

| | 6 | 36 | c | |

| | 10 | 40 | d | |

| | 1 | 31 | f | |

| | 2 | 32 | zz |

+-+

6 rows in set (0.00 sec)

From the lock information above, we can see that although the SQL statement needs to delete multiple records, SESSION 2 is only displayed on records with a primary key health value

Blocked, which proves that MYSQL INNODB is added by a single record, which requires cost execution in the first record that meets the filtering criteria

The second record that meets the filtering criteria will be locked after the DELETE.

After the execution of the SESSION 1 SQL, all records that meet the filtering criteria are locked, and transactions that are not committed or rolled back are not released.

SESSINO 1 and SESSION 2 roll back the transaction.

Conclusion:

When different columns are not indexed, SESSION 1 uses ID,SESSION 2 in WHERE sentences and ID2 in WHERE sentences

Both SESSION 1 and SESSION 2 require that the entire primary key be locked, and blocking occurs when the two SESSION updates different records.

SESSINO 1:

Mysql > select * from T2

+-+

| | id | id2 | name | |

+-+

| | 15 | 45 | a |

| | 3 | 33 | b | |

| | 6 | 36 | c | |

| | 10 | 40 | d | |

| | 1 | 31 | f | |

| | 2 | 32 | zz |

+-+

6 rows in set (0.00 sec)

Mysql > delete from T2 where id=2

Query OK, 1 row affected (0.00 sec)

SESSINO 2:

Delete from T2 where id2=45; = = > blocked

SESSION 5: view lock information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30585

Waiting_thread: 2

Wait_time: 9

Waiting_query: delete from t2 where id2=45

Waiting_table_lock: `test`.`t2`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30584

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 22

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30585:250:3:3

Lock_trx_id: 30585

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 3

Lock_data: 'zz'

* 2. Row * *

Lock_id: 30584:250:3:3

Lock_trx_id: 30584

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t2`

Lock_index: PRIMARY

Lock_space: 250

Lock_page: 3

Lock_rec: 3

Lock_data: 'zz'

2 rows in set (0.00 sec)

From the lock information above, you can see that SESSION 1 only locks a record with a primary key value of zz. SESSION 2 is blocked in

On this record.

SESSION 1 and SESSION 2 roll back the transaction.

Conclusion:

If there is no index on the id column, SQL will take a full scan of the clustered index to filter, because the filtering is done at the MySQL Server level.

So every record, whether it meets the condition or not, is locked with an X. However, for the sake of efficiency, MySQL has been optimized for

Records that do not meet the conditions will be locked after judgment, and the final hold is the lock on the records that meet the conditions, but do not meet the conditions.

The lock / unlock action on the record is not omitted. At the same time, optimization violates the constraints of 2PL.

Combination five: id primary key + RR, locking is consistent with combination one: [id primary key, Read Committed].

Combination six: id unique index + RR, consistent with combination two: [id unique index, Read Committed].

Combination 7: id non-unique index + RR

Delete from t1

Insert into T1 values (2), (6)), (10)), (10), (11)), (15)

Alter table T1 add INDEX idx_id (id)

Mysql > show index from T1\ G

* * 1. Row *

Table: t1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

* 2. Row * *

Table: t1

Non_unique: 1

Key_name: idx_id

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.00 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | f | |

| | 2 | zz |

| | 3 | b | |

| | 6 | c |

| | 10 | d | |

| | 15 | a |

+-+ +

6 rows in set (0.00 sec)

SESSINO 1:

Set global tx_isolation='REPEATABLE-READ'

Set session tx_isolation='REPEATABLE-READ'

Select @ @ global.tx_isolation,@@tx_isolation\ G

* * 1. Row *

@ @ global.tx_isolation: REPEATABLE-READ

@ @ tx_isolation: REPEATABLE-READ

1 row in set (0.00 sec)

Set autocommit=0

Delete from T1 where id = 10

SESSINO 2:

Set global tx_isolation='REPEATABLE-READ'

Set session tx_isolation='REPEATABLE-READ'

Set autocommit=0

Set session innodb_lock_wait_timeout=1000000

Insert into T1 values (10); = > blocked

SESSION 5 view lock information:

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30617

Waiting_thread: 2

Wait_time: 20

Waiting_query: insert into T1 values (10maxiaa')

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 30618

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 27

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30617 ID 24814 ID 8 = > SESSION 2 lock

Lock_trx_id: 30617

Lock_mode: X Gap = > SESSION 2 applies for lock modes X and GAP (gap lock)

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 8

Lock_data: 10,'b'

* 2. Row * *

Lock_id: 30618:248:4:8

Lock_trx_id: 30618

Lock_mode: X = = > although the lock information of SESSION 1 does not show GAP mode, in fact SESSION 1 has a GAP lock

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 8

Lock_data: 10,'b'

2 rows in set (0.00 sec)

SESSINO 2:Ctrl+C terminates SQL and rolls back the transaction

Mysql > insert into T1 values (10mai Zaa')

^ CCtrl-C-sending "KILL QUERY 2" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

SESSION 2:

Insert into T1 values; = = > blocked

SESSION 5 view lock information:

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30624

Waiting_thread: 2

Wait_time: 17

Waiting_query: insert into T1 values (7 mai Zhe ab')

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: idx_id

Blocking_trx_id: 30618

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 626

Blocking_query: NULL

1 row in set (0.00 sec)

SESSION 1 blocks SESSION 2.

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30624:248:4:8

Lock_trx_id: 30624

Lock_mode: X Gap = > SESSION 2 applies for lock modes X and GAP (gap lock)

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 8

Lock_data: 10,'b'

* 2. Row * *

Lock_id: 30618:248:4:8

Lock_trx_id: 30618

Lock_mode: X = = > although the lock information of SESSION 1 does not show GAP mode, in fact SESSION 1 has a GAP lock

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: idx_id

Lock_space: 248

Lock_page: 4

Lock_rec: 8

Lock_data: 10,'b'

2 rows in set (0.00 sec)

SESSINO2:

Mysql > insert into T1 values (7 mai Zhe ab')

^ CCtrl-C-sending "KILL QUERY 2" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

Insert into T1 values (3 recordings bb'); = > executed successfully

Commit

Conclusion:

From the above test, it is inferred that the gap lock is only added on the idx_id index, and there is no gap lock on the primary key.

Combination eight: id No Index + RR

Delete from t1 where id=3

Commit

Alter table t1 drop index idx_id

Mysql > show index from T1\ G

* * 1. Row *

Table: t1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

SESSION 1:

Delete from T1 where id = 10

SESSION 2:

Insert into T1 values (3); = > blocked

SESSION 3:

Insert into T1 values (100) = > blocked

SESSION 5 view lock information:

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30646

Waiting_thread: 2

Wait_time: 311

Waiting_query: insert into T1 values (3mai Zhe bb')

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30641

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 340

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 30647

Waiting_thread: 3

Wait_time: 32

Waiting_query: insert into T1 values (100m)

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30641

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 340

Blocking_query: NULL

2 rows in set (0.00 sec)

SESSION 1 blocks SESSION 2 and SESSION 3.

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30647:248:3:1

Lock_trx_id: 30647

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 1

Lock_data: supremum pseudo-record = = > SESSION 3 is blocked by a gap lock that SESSION 1 adds to the primary key that exceeds the maximum value of the field.

* 2. Row * *

Lock_id: 30641:248:3:1

Lock_trx_id: 30641

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 1

Lock_data: supremum pseudo-record = = > Gap lock that exceeds the maximum value of the field

* 3. Row * *

Lock_id: 30646:248:3:3

Lock_trx_id: 30646

Lock_mode: X,GAP

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 3

Lock_data:'c'= = > SESSION 2 is blocked by a gap lock added in SESSION 1 between name='c' and name='b' on the primary key.

* * 4. Row *

Lock_id: 30641:248:3:3

Lock_trx_id: 30641

Lock_mode: X = = > although the lock information of SESSION 1 does not show GAP mode, in fact SESSION 1 has a GAP lock

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 3

Lock_data:'c'

4 rows in set (0.00 sec)

Conclusion:

In the case where the ID field does not have an index, a gap lock is added to the entire index. No other session can insert new data.

Question: is there a row lock on the entire primary key?

SESSION 2 terminates SQL and rolls back the session

Mysql > insert into T1 values (3mai Zhe bb')

^ CCtrl-C-sending "KILL QUERY 2" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

SESSION 3 terminates SQL and rolls back the session

Mysql > insert into T1 values (100)

^ CCtrl-C-sending "KILL QUERY 3" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

SESSINO 2:

Delete from T1 where id=2; = = > blocked

SESSION 5 view lock information:

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 30649

Waiting_thread: 2

Wait_time: 27

Waiting_query: delete from t1 where id=2

Waiting_table_lock: `test`.`t1`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 30641

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 1500

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

* * 1. Row *

Lock_id: 30649:248:3:2

Lock_trx_id: 30649

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data:'a'= = > SESSION 2 is blocked by SESSION 1 on a record with a primary key.

* 2. Row * *

Lock_id: 30641:248:3:2

Lock_trx_id: 30641

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t1`

Lock_index: PRIMARY

Lock_space: 248

Lock_page: 3

Lock_rec: 2

Lock_data:'a'

2 rows in set (0.00 sec)

The SQL executed by SESSION 1 is delete from T1 where id = 10, and the primary key value is b

The SQL executed by SESSION 2 is delete from T1 where id=2, and the primary key value is zz.

The lock information now shows that SESSION 1 holds an exclusive row lock on a record with a primary key, proving that the RR isolation level ID column does not have an index

Exclusive row locks and gap locks are added to the entire watch.

The answers to the analytical questions on how to handle MySQL locking are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.