从而减少锁冲突的机会;
选择合理的事务大小,小事务发生锁冲突的几率也更小;
给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;
不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;
不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;
对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。
2、Case2:诡异的 Lock wait timeout
连续几天凌晨6点和早上8点 都分别有一个任务失败,load data local infile 的时候报 Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 异常,和平台的同学沟通得知,这是我们自己的业务数据库的 Lock 时间太短或者锁冲突的问题。但是回头一想不应该啊?这不一直好好的吗?而且基本都是单表单任务,不存在多人冲突。
甭管谁的问题,那咱们还是先看自己的数据库有没有问题:
show variables like 'innodb_lock_wait_timeout';
+--------------------------+---------+
| Variable_name | Value |
|--------------------------+---------|
| innodb_lock_wait_timeout | 50 |
+--------------------------+---------+
默认 lock 超时时间 50s,这个时间真心不短了,估计调了也没用,事实上确实死马当活马医的试了下没用。。。
而且这次 SHOW ENGINE INNODB STATUS\G 也没出现任何死锁信息,然后又将目光转向 MySQL-server 日志,希望能从日志里看一看那个时刻前后数据究竟在做什么操作。这里先简单的介绍下MySQL日志文件系统的组成:
(a) error 日志:记录启动、运行或停止 mysqld 时出现的问题,默认开启。
(b) general 日志:通用查询日志,记录所有语句和指令,开启数据库会有 5% 左右性能损失。
(c) binlog 日志:二进制格式,记录所有更改数据的语句,主要用于 slave 复制和数据恢复。
(d) slow 日志:记录所有执行时间超过 long_query_time 秒的查询或不使用索引的查询,默认关闭。
(e) Innodb日志:innodb redo log、undo log,用于恢复数据和撤销操作。
从上面的介绍可以看到,目前这个问题的日志可能在 d 和 b 中,看了下 d 中没有,那就只能开启 b 了,但 b 对数据库的性能有一定损耗,由于是全量日志,量非常巨大,所以开启一定要谨慎:
-- general_log 日志默认关闭,开启会影响数据库 5% 左右性能:
show variables like 'general%';
+------------------+---------------------------------+
| Variable_name | Value |
|------------------+---------------------------------|
| general_log | OFF |
| general_log_file | /opt/data/mysql/tjtx-103-26.log |
+------------------+---------------------------------+
-- 全局 session 级别开启:
set global general_log=1
-- 如果需要对当前 session 生效需要:
set general_log=1
-- set 指令设置的动态参数在 MySQL 重启后失效,如果需要永久生效需要在 /etc/my.cnf 中配置静态变量/参数。
-- 如果不知道 my.cnf 位置,可以根据 mysql -? | grep ".cnf" 查询
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
我这里只是每天在出问题的前后半小时开启下全量日志,结果没有发现任何 MySQL-client 请求到我们的业务数据库!该日志格式如下,记录了所有的连接与命令:
/usr/sbin/mysqld, Version: 5.7.12-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /opt/data/mysql/mysql.sock
Time Id Command Argument
2017-07-20T21:45:01.880828Z28556028 Quit
2017-07-20T21:45:02.708621Z28401469 Query SELECT 1
2017-07-20T21:45:02.736734Z28556029 Connect ooxx@127.0.0.1 on db_zz_system using TCP/IP
2017-07-20T21:45:02.737242Z28556029 Query /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_ca