Using Innodb_monitor to monitor blocking samples

Simulate a scene:
To build a InnoDB engine table:
CREATE TABLE Shuzhi
(
ID Mediumint (8) Primary key,
Name varchar (30),
Shuzhi Mediumint (10)
);
ALTER TABLE Shuzhi ENGINE=INNODB;
Insert Some records:
INSERT into Shuzhi values (1, ' AA ', 1000);
INSERT into Shuzhi values (2, ' BB ', 2000);
INSERT into Shuzhi values (3, ' CC ', 3000);
INSERT into Shuzhi values (4, ' DD ', 4000);
INSERT into Shuzhi values (5, ' ee ', 5000);
INSERT into Shuzhi values (6, ' FF ', 6000);
INSERT into Shuzhi values (7, ' GG ', 7000);
INSERT into Shuzhi values (8, ' hh ', 8000);
To confirm the isolation level of a transaction:
Mysql> SELECT @ @global. tx_isolation;
+-----------------------+
| @ @global. tx_isolation |
+-----------------------+
| Repeatable-read |
+-----------------------+
1 row in Set (0.00 sec)
Mysql>
To facilitate testing, extend the lock wait timeout to 7,200 seconds (default is 50 seconds):
Note: Only for the purpose of practice, experiment, in their own experimental environment, to make such changes.
Mysql> SELECT @ @global. innodb_lock_wait_timeout;
+-----------------------------------+
| @ @global. innodb_lock_wait_timeout |
+-----------------------------------+
| 50 |
+-----------------------------------+
1 row in Set (0.00 sec)
Mysql>
After "[Mysqld]" of the My.ini file, add: innodb_lock_wait_timeout=7200
Restart the database.
Mysql> SELECT @ @global. innodb_lock_wait_timeout;
+-----------------------------------+
| @ @global. innodb_lock_wait_timeout |
+-----------------------------------+
| 7200 |
+-----------------------------------+
1 row in Set (0.00 sec)
Mysql>
Opens a session (Session 1), starts a transaction, but does not release the lock:
Start transaction;
Update Shuzhi set shuzhi=1500 where id=1;
Opens a session (Session 2), executes a script (2.sql), and the transaction in this script is locked by the transaction of Session 1:
2.sql:
Start transaction;
Update Shuzhi set name= ' aaaa ' where id=1;
Commit
Opens a session (Session 3), executes a script (3.sql), and the transaction in this script is locked by the preceding transaction:
3.sql:
Start transaction;
Update Shuzhi set shuzhi= ' 7500 ' where name= ' GG ';
Commit
Because no index is built on the name column, this SQL statement will request a table-level lock, so it is locked.
Note: When using the InnoDB engine, row-level locks are used only for SQL statements that actually use the index, or table-level locks are used to lock the entire table.
Opens a session (session 4), executes a script (4.sql), and the transaction in this script executes successfully:
4.sql:
Start transaction;
Update Shuzhi set shuzhi= ' 5500 ' where id=5;
Commit
Session 4 is not blocked because session 3 is waiting for a table-level lock (not yet requested).
Opens a session (session 5), executes a script (5.sql), and the transaction in this script is locked by the preceding transaction:
5.sql:
Start transaction;
SELECT * from Shuzhi lock in share mode;
Commit

Handle Lock contention:

View the current connection information through the show Processlist\g command (you can get more complete information by using the Watch full processlist\g):
Mysql> Show Processlist\g
1. Row ***************************
Id:1
User:root
host:127.0.0.1:7014
Db:db1
Command:sleep
Time:37
State:
Info:null
2. Row ***************************
Id:2
User:root
host:127.0.0.1:8903
Db:db1
Command:query
time:2099
State:updating
Info:update Shuzhi set name= ' AAAA ' where id=1
3. Row ***************************
Id:3
User:root
host:127.0.0.1:8909
Db:db1
Command:query
time:1759
State:updating
Info:update Shuzhi set shuzhi= ' 7500 ' where name= ' GG '
4. Row ***************************
Id:4
User:root
host:127.0.0.1:8928
Db:db1
Command:sleep
time:1090
State:
Info:null
5. Row ***************************
Id:5
User:root
host:127.0.0.1:8940
Db:db1
Command:query
time:383
State:sending data
Info:select * from Shuzhi lock in Share mode
6. Row ***************************
Id:6
User:root
host:127.0.0.1:8945
Db:db1
Command:query
time:0
State:null
Info:show processlist
6 rows in Set (0.00 sec)
Mysql>
We can see that sessions 2, 3, 5 are in the state of being executed (blocked).
From the Time column (execution times), we can roughly see that the session with Id 2 is blocked for the longest time (because it is the longest execution time).
So who is blocking session 2?
In other words, which session is the culprit for blocking?

We can view it through the show InnoDB status\g command (shown InnoDB status in the future version of MySQL will be replaced by "engine InnoDB status")
But show InnoDB status\g command display results, a screen is not necessarily displayed under, also is not conducive to real-time, continuous monitoring.
So we need to write the results shown in the show InnoDB status\g command to the Err log.
The method is to open the InnoDB monitor monitors:
CREATE TABLE Innodb_monitor (a INT) Engine=innodb;
When the monitor is turned on, the monitored content is recorded in the Err log every 15 seconds by default (the information displayed by the Show InnoDB Status command).
If you open it for a long time, it can cause the. err file to become very large.
So after monitoring, be sure to turn off the monitor:
DROP TABLE Innodb_monitor;

Here's a partial message showing the results of the show InnoDB status\g command:
---TRANSACTION 0 39635973, ACTIVE 583 sec, OS thread ID 1824 starting index read
MySQL tables in use 1, locked 1
Lock wait 2 lock struct (s), heap size, 1 row lock (s)
MySQL thread ID 5, query ID notoginseng 127.0.0.1 root sending data
SELECT * from Shuzhi lock in Share mode
-------TRX has BEEN waiting 583 SEC for this LOCK to be granted:
Record LOCKS Space ID 0 page no 153626 n bits index ' PRIMARY ' of table ' db1 '. ' Shuzhi ' Trx ID 0 39635973 Lock mode S Wai Ting
......
---TRANSACTION 0 39635970, ACTIVE 1959 sec, OS thread ID 4396 starting index read
MySQL tables in use 1, locked 1
Lock wait 2 lock struct (s), heap size, 1 row lock (s)
MySQL thread ID 3, query ID 127.0.0.1 root updating
Update Shuzhi set shuzhi= ' 7500 ' where name= ' GG '
-------TRX has BEEN waiting 1959 SEC for this LOCK to be granted:
Record LOCKS Space ID 0 page no 153626 n bits index ' PRIMARY ' of table ' db1 '. ' Shuzhi ' Trx ID 0 39635970 Lock_mode X Wai Ting
......
---TRANSACTION 0 39635969, ACTIVE 2299 sec, OS thread ID 6100 starting index read
MySQL tables in use 1, locked 1
Lock wait 2 lock struct (s), heap size, 1 row lock (s)
MySQL thread ID 2, query ID 127.0.0.1 root updating
Update Shuzhi set name= ' AAAA ' where id=1
-------TRX has BEEN waiting 2299 SEC for this LOCK to be granted:
Record LOCKS Space ID 0 page no 153626 n bits index ' PRIMARY ' of table ' db1 '. ' Shuzhi ' Trx ID 0 39635969 lock_mode X Loc KS Rec but not gap waiting
......
---TRANSACTION 0 39635968, ACTIVE 2347 sec, OS thread ID 1168
2 lock struct (s), heap size, 1 row lock (s), undo log Entries 1
MySQL thread ID 1, query ID 127.0.0.1 root
Show InnoDB Status

From this we can see:
"MySQL thread ID 5", "MySQL thread ID 3", and "MySQL thread ID 2" are in the waiting state ("Lock wait 2 lock struct (s)").
The "MySQL thread ID 1" is holding the lock ("2 lock struct (s)").
and the transaction number of MySQL thread ID 1 is smaller than several other sessions ("TRANSACTION 0 39635968"),
This shows that the "MySQL thread ID 1" transaction was first executed compared to several other blocked sessions.
Therefore, it can be judged that "MySQL thread ID 1" (Session 1) blocks transactions for other sessions.

Notifies users who use session 1 to commit or rollback transactions immediately.
If a user using session 1 reports that "a client failure, a transaction cannot be committed or rolled back", you can kill the session:
Mysql> kill 1;
Note: Number 1 is "show processlist\g" displays the "id:1" in the result.


So far, the experiment is over.




Add a small test (several cases that block INSERT statements):
SELECT * from Shuzhi for update;
Because it is a table-level exclusive lock (X), it blocks:
INSERT into Shuzhi values (9, ' II ', 9000);
==================================================================
SELECT * from Shuzhi lock in share mode;
Because it is a table-level shared lock (S), it blocks:
INSERT into Shuzhi values (ten, ' JJ ', 10000);
==================================================================
One of the most easily overlooked situations:
Update Shuzhi set shuzhi= ' 8500 ' where name= ' hh ';
Because no index is built on the Name column, a table-level exclusive lock (X) is used and therefore blocks:
INSERT into Shuzhi values (one, ' KK ', 11000);
--This article from: http://blog.itpub.net/29612462/viewspace-1162167