mysql锁的问题

  处理mysql锁争用的一个示例 

分类： MySQL

 

处理mysql锁争用的一个示例

模拟一个场景：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `shuzhi`;
CREATE TABLE `shuzhi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shuzi` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;


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);

select version();

SHOW INDEX from shuzhi;

-------------------准备条件-------------------
mysql>
为了方便测试，将锁等待超时时间延长到7200秒（默认为50秒）：
注意：仅仅是出于练习、实验的目的，在自己的实验环境下，才做这样的修改。

mysql>

mysql>
在my.ini文件的“[mysqld]”之后添加：innodb_lock_wait_timeout=7200
重启数据库。这样的话就可以把超时时间加长为7200了

 

 

-------------开始实验--------------

1.开启一个会话（会话1），开始一个事务，但不释放锁：【会一直等待7200毫秒，where条件有索引，所以行锁】

start transaction;
update shuzhi set shuzhi=1500 where id=1;

2.开启一个会话（会话2），执行一个脚本（2.sql），此脚本中的事务会被会话1的事务锁定：

start transaction;
update shuzhi set name='aaaa' where id=1;
commit;

 

3.开启一个会话（会话3），执行一个脚本（3.sql），此脚本中的事务会被前面的事务锁定：
mysql>

start transaction;
update shuzhi set shuzhi='7500' where name='gg';
commit;

由于没有在name列上建索引，这条sql语句将会申请一个表级锁，所以会被锁定。
注意：在使用innodb引擎时，只有真正使用到索引的sql语句才会使用行级锁，否则将使用表级锁，锁定整张表！

4.开启一个会话（会话4），执行一个脚本（4.sql），此脚本中的事务会执行成功：
mysql>

start transaction;
update shuzhi set shuzhi='5500' where id=5;
commit;

由于会话3是在等待表级锁（还没有申请到），因此不会阻塞会话4。

5.开启一个会话（会话5），执行一个脚本（5.sql），此脚本中的事务会被前面的事务锁定：
mysql>

start transaction;
select * from shuzhi lock in share mode;
commit;

处理锁争用：

通过show processlist\G命令查看当前连接信息（可以通过show full processlist\G获得更完整的信息）：
 

show  PROCESSLIST;

id	userj	Host	db	Command	Time	State	Info
1	root	127.0.0.1:7014	db1	Sleep	37		NULL
2	root	127.0.0.1:8903	db1	Query	2099	Updating	update shuzhi set name='aaaa' where id=1
3	root	127.0.0.1:8909	db1	Query	1759	Updating	update shuzhi set shuzhi='7500' where name='gg'
4	root	127.0.0.1:8928	db1	Sleep	1090		NULL
5	root	127.0.0.1:8940	db1	Query	383	Sending data	select * from shuzhi lock in share mode
6	root	127.0.0.1:8945	db1	Query	0	NULL	show processlist

6 rows in set (0.00 sec)

我们可以看出会话2、3、5都处于正在执行的状态（被阻塞）。
从“Time”列（执行时间），我们大致可以看出“Id”为2的会话被阻塞的时间似乎最长（因为执行时间最长）。
那么究竟是谁阻塞了会话2呢？
换句话说，究竟哪个会话是造成阻塞的罪魁祸首呢？

我们可以通过Show innodb status命令来查看。

SHOW engine INNODB STATUS;

但是Show innodb status 命令显示的结果，一屏未必显示的下，也不利于实时、持续的监控。
因此我们需要

----------------把Show innodb status 命令显示的结果写入err日志中-----------------
方法是打开InnoDB Monitor监视器：
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

【 通知innodb存储引擎将数据输出到/etc/my.cnf中定义的log-erroe文件里】

打开监视器以后， 默认情况下每15秒会向err日志中记录监控的内容（Show innodb status命令显示的信息） 。
如果长时间打开，会导致.err文件变得非常的巨大。
因此监控完后，一定要记得关闭监视器：
DROP TABLE innodb_monitor;

下面贴出SHOW engine INNODB STATUS; 命令显示结果的部分信息：

---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 ”显示结果中的“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);

 

 

 

 

 

mysql 线程等待时间，解决sleep进程过多的办法

 

show full processlist;

 

如果你没有修改过MySQL的配置，缺省情况下，wait_timeout的初始值是28800【48分钟，我也是醉了！】

 

如果你没有修改过MySQL的配置，缺省情况下，wait_timeout的初始值是28800。

wait_timeout 过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，

不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题，通常来说，我觉得把wait_timeout设置为10是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

 

 

设置方式一：修改my.cnf文件【缺点：需要重启数据库】

# vi /etc/my.cnf

[mysqld]

wait_timeout=10

 

# /etc/init.d/mysql restart

 

 

 

 

设置方式二：命令行执行【不需要重启数据库】

mysql> set global wait_timeout=10;

 

mysql> show global variables like 'wait_timeout';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| wait_timeout               | 10    |

+----------------------------+-------+

 

MySQL大拿Jeremy Zawodny曾在他的文章Fixing Poor MySQL Default Configuration Values里面列出了几个很恶心的MySQL缺省设置，不过没包含wait_timeout，但我觉得它也应该算一个，每次新装MySQL后最好都记得修改它。

 

 

睡眠连接过多，会对mysql服务器造成什么影响？

 

严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

 

造成睡眠连接过多的原因？

 

1.　使用了太多持久连接（个人觉得，在高并发系统中，不适合使用持久连接）

 

2.　程序中，没有及时关闭mysql连接

 

3.　数据库查询不够优化，过度耗时。

 

那么，如果要从根本上解决sleep连接过多，就得从以上三点反复检查，但是见效并不快。

 

网上有人分享，使用shell脚本配合cron,定期杀死睡眠时间太久的连接，但是这种方法非常不可取，典型的以暴制暴，很可能导致数据崩溃，而且，还需要编写相应shell, 设置cron,　实施成本较繁琐，不推荐使用。

 

那么更好的办法应该是让mysql自己决定这些睡眠连接的命运，实施会更简单，有效。

 

mysql的配置文件中，有一项：

 

wait_timeout, 即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止，多好的办法！

 

如设置：　

 

wait_timeout=100 #即设置mysql连接睡眠时间为100秒，任何sleep连接睡眠时间若超过100秒，将会被mysql服务自然终止，要比编写shell脚本更简单。

 

那么，对于正在运行中的生产服务器，在不能停止服务情况下，修改此项怎么办？很简单，以root用户登录到mysql,执行：

 

set global wait_timeout=100

 

即可。

 

在我的生产环境中，使用这个办法，取得了相当好的效果。

 

当然，更根本的方法，还是从以上三点排查之：

 

1.　程序中，不使用持久链接，即使用mysql_connect而不是pconnect。

 

2.   程序执行完毕，应该显式调用mysql_close

 

3.　只能逐步分析系统的SQL查询，找到查询过慢的SQL,优化之