处理mysql锁争用的一个示例

处理mysql锁争用的一个示例

转自 http://blog.itpub.net/29612462/viewspace-1162167

模拟一个场景：
建一个innodb引擎的表：
create table shuzhi
(
  id mediumint(8) primary key,
  name varchar(30),
  shuzhi mediumint(10)
);
alter table shuzhi engine=innodb;
插入一些记录：
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);
确认事务的隔离级别：
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)
mysql>
为了方便测试，将锁等待超时时间延长到7200秒（默认为50秒）：
注意：仅仅是出于练习、实验的目的，在自己的实验环境下，才做这样的修改。
mysql> select @@global.innodb_lock_wait_timeout;
+-----------------------------------+
| @@global.innodb_lock_wait_timeout |
+-----------------------------------+
|                                50 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
在my.ini文件的“[mysqld]”之后添加：innodb_lock_wait_timeout=7200
重启数据库。
mysql> select @@global.innodb_lock_wait_timeout;
+-----------------------------------+
| @@global.innodb_lock_wait_timeout |
+-----------------------------------+
|                              7200 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
开启一个会话（会话1），开始一个事务，但不释放锁：
start transaction;
update shuzhi set shuzhi=1500 where id=1;
开启一个会话（会话2），执行一个脚本（2.sql），此脚本中的事务会被会话1的事务锁定：
2.sql：
start transaction;
update shuzhi set name='aaaa' where id=1;
commit;
开启一个会话（会话3），执行一个脚本（3.sql），此脚本中的事务会被前面的事务锁定：
3.sql：
start transaction;
update shuzhi set shuzhi='7500' where name='gg';
commit;
由于没有在name列上建索引，这条sql语句将会申请一个表级锁，所以会被锁定。
注意：在使用innodb引擎时，只有真正使用到索引的sql语句才会使用行级锁，否则将使用表级锁，锁定整张表！
开启一个会话（会话4），执行一个脚本（4.sql），此脚本中的事务会执行成功：
4.sql：
start transaction;
update shuzhi set shuzhi='5500' where id=5;
commit;
由于会话3是在等待表级锁（还没有申请到），因此不会阻塞会话4。
开启一个会话（会话5），执行一个脚本（5.sql），此脚本中的事务会被前面的事务锁定：
5.sql：
start transaction;
select * from shuzhi lock in share mode;
commit;

处理锁争用：

通过show processlist\G命令查看当前连接信息（可以通过show 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>
我们可以看出会话2、3、5都处于正在执行的状态（被阻塞）。
从“Time”列（执行时间），我们大致可以看出“Id”为2的会话被阻塞的时间似乎最长（因为执行时间最长）。
那么究竟是谁阻塞了会话2呢？
换句话说，究竟哪个会话是造成阻塞的罪魁祸首呢？

我们可以通过Show innodb status\G命令来查看。
但是Show innodb status\G命令显示的结果，一屏未必显示的下，也不利于实时、持续的监控。
因此我们需要把Show innodb status\G命令显示的结果写入err日志中。
方法是打开InnoDB Monitor监视器：
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
打开监视器以后，默认情况下每15秒会向err日志中记录监控的内容（Show innodb status命令显示的信息）。
如果长时间打开，会导致.err文件变得非常的巨大。
因此监控完后，一定要记得关闭监视器：
DROP TABLE innodb_monitor;

下面贴出Show innodb status\G命令显示结果的部分信息：
---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 320, 1 row lock(s)
MySQL thread id 5, query id 37 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 80 index `PRIMARY` of table `db1`.`shuzhi` trx id 0 39635973 lock mode S waiting
......
---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 320, 1 row lock(s)
MySQL thread id 3, query id 22 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 80 index `PRIMARY` of table `db1`.`shuzhi` trx id 0 39635970 lock_mode X waiting
......
---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 320, 1 row lock(s)
MySQL thread id 2, query id 15 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 80 index `PRIMARY` of table `db1`.`shuzhi` trx id 0 39635969 lock_mode X locks rec but not gap waiting
......
---TRANSACTION 0 39635968, ACTIVE 2347 sec, OS thread id 1168
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1, query id 48 127.0.0.1 root
show innodb status

从中我们可以看到：
“MySQL thread id 5”，“MySQL thread id 3”，“MySQL thread id 2”都处于等待状态（“LOCK WAIT 2 lock struct(s)”）。
而“MySQL thread id 1”正在持有锁（“2 lock struct(s)”）。
并且“MySQL thread id 1”的事务编号比其它几个会话都小（“TRANSACTION 0 39635968”），
这说明与其它几个被阻塞的会话相比，“MySQL thread id 1”的事务是最先执行的。
因此可以判断：是“MySQL thread id 1”（会话1）阻塞了其它会话的事务。

通知使用会话1的用户立即提交或回退事务。
如果使用会话1的用户报告说：“客户端故障，事务无法正常提交或回退”，那么可以kill掉该会话：
mysql> kill 1;
注：编号1为“show processlist\G”显示结果中的“Id: 1”。


至此，实验结束。




补充一个小测试（阻塞insert语句的几种情况）：
select * from shuzhi for update;
由于是表级排他锁（X），因此会阻塞：
insert into shuzhi values(9,'ii',9000);
==================================================================
select * from shuzhi lock in share mode;
由于是表级共享锁（S），因此会阻塞：
insert into shuzhi values(10,'jj',10000);
==================================================================
最容易被忽视的一种情况：
update shuzhi set shuzhi='8500' where name='hh';
由于name列上没建索引，因此会使用到表级排他锁（X），因此会阻塞：
insert into shuzhi values(11,'kk',11000);