原 【转】MySQL 死锁与日志二三事
1695 | 0 | 0
SHOW ENGINE INNODB STATUS\G
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017-07-17 04:09:01 0x7f6de03c8700 *** (1) TRANSACTION: TRANSACTION 215208479, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 27844824, OS thread handle 140092183037696, query id 412503674 10.126.95.84 zeye Searching rows for update update t_channel_final_datas set nr_register=133,nr_add_goods=29,nr_order_normal=11,nr_pay_normal=8,nr_order_special=0,nr_pay_special=0,n_add_user_num=16 where count_date='2017-07-16' and channel_id='16' and channel_type='10' and terminal='26' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208479 lock_mode X locks rec but not gap waiting Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 1: len 1; hex 30; asc 0;; 2: len 4; hex 80000010; asc ;; 3: len 4; hex 8009055e; asc ^;; *** (2) TRANSACTION: TRANSACTION 215208474, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 6 lock struct(s), heap size 1136, 7 row lock(s) MySQL thread id 27844825, OS thread handle 140109890225920, query id 412503669 10.135.6.41 zeye Searching rows for update update t_channel_final_datas set nr_register=24,nr_add_goods=32,nr_order_normal=0,nr_pay_normal=0,nr_order_special=0,nr_pay_special=0,n_add_user_num=11 where count_date='2017-07-16' and channel_id='114' and channel_type='10' and terminal='116' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 1: len 1; hex 30; asc 0;; 2: len 4; hex 80000010; asc ;; 3: len 4; hex 8009055e; asc ^;; ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 464 page no 4743 n bits 264 index PRIMARY of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap waiting Record lock, heap no 168 PHYSICAL RECORD: n_fields 32; compact format; info bits 0 0: len 4; hex 80090569; asc i;; 1: len 6; hex 00000cd3b9d0; asc ;; ... *** WE ROLL BACK TRANSACTION (1)
show INDEX from `db_zz_flow`.`t_channel_final_datas`; +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_namt | Packed | Null | Index_type | Comment | Index_comment | |-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------| | t_channel_final_datas | 0 | PRIMARY | 1 | id > | <null> | | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 1 | count_date> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 2 | channel_ty> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 3 | terminal > | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_channelid | 1 | count_date> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_channelid | 2 | channel_id> | <null> | YES | BTREE | | | +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+
show variables like 'autocommit'; +-----------------+---------+ | Variable_name | Value | |-----------------+---------| | autocommit | ON | +-----------------+---------+
show VARIABLES LIKE 'innodb_rollback_on_timeout' +----------------------------+---------+ | Variable_name | Value | |----------------------------+---------| | innodb_rollback_on_timeout | OFF | +----------------------------+---------+
1.尽量使用较低的隔离级别,比如如果发生了间隙锁,你可以把会话或者事务的事务隔离级别更改为 RC(read committed)级别来避免,但此时需要把 binlog_format 设置成 row 或者 mixed 格式
2.精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
3.选择合理的事务大小,小事务发生锁冲突的几率也更小;
4.给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;
5.不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
6.尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;
7.不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;
8.对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。
show variables like 'innodb_lock_wait_timeout'; +--------------------------+---------+ | Variable_name | Value | |--------------------------+---------| | innodb_lock_wait_timeout | 50 | +--------------------------+---------+
-- 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
/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_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 2017-07-20T21:45:02.738868Z28556029 Query SHOW COLLATION 2017-07-20T21:45:02.739941Z28556029 Query SET character_set_results = NULL 2017-07-20T21:45:02.740275Z28556029 Query SET autocommit=1
# crontab 部署方式: # */1 0-10 * * * cd /opt/ooxx/script && bash mysql_perf.sh >> logs/mysql_perf.log.`date -I` 2>&1 date -Iseconds echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> top -bn1|head' top -bn1|head echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SHOW ENGINE INNODB STATUS\G ' mysql -uroot -pooxx -h127.0.0.1 -e 'SHOW ENGINE INNODB STATUS\G' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show open tables where in_use>0;' mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show full processlist;' mysql -uroot -pooxx -h127.0.0.1 -e 'show full processlist;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; ' mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_locks`;' mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_locks`;' echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show status like '%lock%';" mysql -uroot -pooxx -h127.0.0.1 -e "show status like '%lock%';" echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show global status like "table_locks%";' mysql -uroot -pooxx -h127.0.0.1 -e 'show global status like "table_locks%";' echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..." mysql -uroot -pooxx -h127.0.0.1 -e "select r.trx_isolation_level, r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_trx_thread, r.trx_state waiting_trx_state, lr.lock_mode waiting_trx_lock_mode, lr.lock_type waiting_trx_lock_type, lr.lock_table waiting_trx_lock_table, lr.lock_index waiting_trx_lock_index, r.trx_query waiting_trx_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_trx_thread, b.trx_state blocking_trx_state, lb.lock_mode blocking_trx_lock_mode, lb.lock_type blocking_trx_lock_type, lb.lock_table blocking_trx_lock_table, lb.lock_index blocking_trx_lock_index, b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id\G" echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..." mysql -uroot -pooxx -h127.0.0.1 -e "SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G" echo date -Iseconds echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>==================================================================================' echo file_name=mysql_perf.log.`date -I` if [[ `date +%-H%-M` == 1059 ]] then cd /opt/ooxx/script/logs chmod 777 /home/work/ooxx/$file_name find /home/work/ooxx -name 'mysql_perf.log.*' -mtime +7 -delete cd /opt/data/mysql cp tjtx-ooxx-slow.log /home/work/ooxx/tjtx-ooxx-slow.log.`date -I` chmod 777 /home/work/ooxx/tjtx-ooxx-slow.log.`date -I` find /home/work/ooxx -name 'tjtx-ooxx-slow.log.*' -mtime +7 -delete cp mysqld.log /home/work/ooxx/mysqld.log.`date -I` chmod 777 /home/work/ooxx/mysqld.log.`date -I` find /home/work/ooxx -name 'mysqld.log.*' -mtime +7 -delete fi ################ # 开启 general_log 全量明细日志会降低数据库 5% 性能 #if [[ "`date +%H%M`" == "0545" ]] #then # echo "`date +%H%M` ------- set global general_log=1;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' #elif [[ "`date +%H%M`" == "0630" ]] #then # echo "`date +%H%M` ------- set global general_log=0;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' #elif [[ "`date +%H%M`" == "0745" ]] #then # echo "`date +%H%M` ------- set global general_log=1;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' #elif [[ "`date +%H%M`" == "0830" ]] #then # echo "`date +%H%M` ------- set global general_log=0;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' #elif [[ "`date +%H%M`" == "0001" ]] #then # echo "`date +%H%M` ------- set global general_log=1;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' #elif [[ "`date +%H%M`" == "0002" ]] #then # echo "`date +%H%M` ------- set global general_log=0;" # mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' #fi #[[ 10#`date +%H%M` -lt 10#0550 || 10#`date +%H%M` -gt 10#0830 ]] && echo "`date +%H%M` ------- set global general_log=0;" && mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' # mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;show full processlist;SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;SELECT * FROM `information_schema`.`innodb_locks`;SHOW ENGINE INNODB STATUS\G' # --show variables like '%tx_isolation%'; # --SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation; # -- SET GLOBAL tx_isolation = 'READ-COMMITTED'; # # --show variables like '%timeout%'; # --show variables like 'innodb_lock_wait_timeout'; # -- SET GLOBAL innodb_lock_wait_timeout=60 # # --show variables like 'long_query_time'; # -- SET global long_query_time=3; # -- show variables like 'innodb_rollback_on_timeout'; # -- show VARIABLES like '%max_allowed_packet%'; # -- set global max_allowed_packet = 100*1024*1024; # 自动提交 # -- show variables like 'autocommit'; # 慢查询 # -- show variables like '%slow_query_log%'; # set global 只对当前数据库生效,如果MySQL重启后则会失效。如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此)。 # set global slow_query_log=1; # -- show variables like 'long_query_time%'; # set global long_query_time=4; # show global variables like 'long_query_time'; # select sleep(5); # -- show variables like 'log_queries_not_using_indexes'; # set global log_queries_not_using_indexes=1; # -- show variables like 'log_slow_admin_statements'; # -- show global status like '%Slow_queries%'; # http://www.cnblogs.com/kerrycode/p/5593204.html # -- show variables like "%time_zone%"; #set global time_zone = '+8:00'; #开启general_log日志 # -- show variables like 'general%'; #可以在my.cnf里添加,1开启(0关闭),当然了,这样要重启才能生效,有点多余了 #general-log = 1 #log = /log/mysql_query.log路径 #也可以设置变量那样更改,1开启(0关闭),即时生效,不用重启,首选当然是这样的了 # set global general_log=1 #这个日志对于操作频繁的库,产生的数据量会很快增长,出于对硬盘的保护,可以设置其他存放路径 #set global general_log_file=/tmp/general_log.log #mysql记录客户端IP:init_connect,有super权限的用户是不记录的, # create table t1 ( cur_user varchar(100), n_user varchar(100),in_time timestamp default current_timestamp()) ; # set global init_connect='insert into test.t1 (cur_user,n_user) values (current_user(),user())'; # SHOW CREATE TABLE mysql.general_log\G ,开启general_log日志也行: # https://dba.stackexchange.com/questions/33654/mysql-logging-activity-from-specific-user-or-ip #SELECT REVERSE(SUBSTRING_INDEX(REVERSE(USER()),'@',1)) as ip; #SELECT SUBSTRING(USER(), LOCATE('@', USER())+1) as ip; #select SUBSTRING_INDEX(host,':',1) as 'ip' from information_schema.processlist WHERE ID=connection_id();
文章转自:https://my.oschina.net/leejun2005/blog/1484511
0
一默
3人已关注
领课教育 29723
7910
update 44158
3679
领课教育 16292
husheng 19328
请更新代码 40284
凯哥Java 849
凯哥Java 954
凯哥Java 681