MySQL：一个死锁分析 (未分析出来的死锁)
最近一个朋友给了我一个死锁 没分析出来搞了好几天，但是把以前出现的一个死锁理了一下流程。这里大概记录一下，并且给出朋友的案例。
RC 隔离级别很少出GAP我已经知道的
- 继承和分裂会出LOCK_GAP这是代码写死的
purge线程可能触发
页的分裂融合可能触发
内部回滚可能触发
- 唯一性检查会出LOCK_ORDINARY[next_key_lock]
一、构造死锁
RC RR级别通用
- 死锁表结构和数据
drop table testunj1 ;
create table testunj1 (id1 int primary key,id2 int unique key,name varchar(20));
insert into testunj1 values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng');
mysql> select * from testunj1;
+-----+------+---------+
| id1 | id2 | name |
+-----+------+---------+
| 1 | 1 | gaopeng |
| 10 | 10 | gaopeng |
| 20 | 20 | gaopeng |
+-----+------+---------+
3 rows in set (0.01 sec)- 死锁构造流程
| T1 | T2 | T3 |
|---|---|---|
| begin;insert into testunj1 values(17,17,'gaopeng'); insert into testunj1 values(15,15,'gaopeng'); | ||
| begin; insert into testunj1 values(14,15,'gaopeng');堵塞 | ||
| begin; insert into testunj1 values(16,17,'gaopeng');堵塞 | ||
| rollback; | 成功 | 死锁 |
- 死锁记录
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-08-29 05:03:47 0x7f2fdc6f0700
*** (1) TRANSACTION:
TRANSACTION 7261233, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 139843538720512, query id 583 localhost root update
insert into testunj1 values(14,15,'gaopeng')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261233 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000014; asc ;;1: len 4; hex 80000014; asc ;;*** (2) TRANSACTION:
TRANSACTION 7261234, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 139843538454272, query id 585 localhost root update
insert into testunj1 values(16,17,'gaopeng')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261234 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000014; asc ;;1: len 4; hex 80000014; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261234 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000014; asc ;;1: len 4; hex 80000014; asc ;;*** WE ROLL BACK TRANSACTION (2)
二、分析
这个死锁实际上涉及到锁的继承和分裂我们分析如下两个事物堵塞的案例和加锁步骤，主要弄明白gap lock怎么来的。
set global innodb_lock_wait_timeout=200000;set global innodb_show_verbose_locks=1;set global transaction_isolation =1;重新登陆会话建立表和插入数据如下：drop table testunj1 ;
create table testunj1 (id1 int primary key,id2 int unique key,name varchar(20));
insert into testunj1 values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng'); 如果有debug环境gdb断点： lock_rec_set_nth_bit步骤如下：
| T1 | T2 |
|---|---|
| 阶段1 | |
| BEGIN;insert into testunj1 values(17,17,'gaopeng'); | |
| BEGIN;insert into testunj1 values(16,17,'gaopeng'); 堵塞 | |
| 阶段2 | |
| ROLLBACK; | |
我们只用2个事物来分析流程，实际上流程知道了原因也就知道了。
- 阶段1 T1不提交T2堵塞
- 前奏
T1的插入不上任何锁，因为插入如果下一条记录没有锁，因此是隐含锁。分析从T2
insert into testunj1 values(16,17,'gaopeng'); 堵塞开始
- 第一步 T2执行 insert into testunj1 values(16,17,'gaopeng'); 步骤1
T2帮助T1隐士锁转换 上LOCK_X，这里通过函数lock_rec_convert_impl_to_expl 进行转换。
栈帧如下：
(gdb) bt
#0 lock_rec_set_nth_bit (lock=0x3054068, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10c95a0, index=0x7fffa89e3410, mode=1059, rec_id=..., size=9)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484
#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d59d20, trx=0x7ffff10c95a0, owns_trx_mutex=false, add_to_hash=true, prdt=0x0)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537
#3 0x0000000001a40152 in lock_rec_add_to_queue (type_mode=1059, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, trx=0x7ffff10c95a0, caller_owns_trx_mutex=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853
#4 0x0000000001a49cee in lock_rec_convert_impl_to_expl_for_trx (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, trx=0x7ffff10c95a0, heap_no=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6180
#5 0x0000000001a4a124 in lock_rec_convert_impl_to_expl (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6242
#6 0x0000000001a4a9f6 in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, mode=LOCK_S, gap_mode=0, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6446
#7 0x0000000001aeff23 in row_ins_set_shared_rec_lock (type=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483
#8 0x0000000001af108d in row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, s_latch=false, mtr=0x7ffff0d5aec0, offsets_heap=0x7fff9c02ef08) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115
#9 0x0000000001af3440 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c02ef08, heap=0x7fff9c00e918, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034
#10 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421
#11 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470
#12 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618
#13 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760
#14 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945
#15 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283
#16 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406
#17 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020")at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344
#18 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466
#19 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881
#20 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773
#21 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121
#22 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746
#23 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901
#24 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY)at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490
#25 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021
#26 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312
#27 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190
---Type <return> to continue, or q <return> to quit---
#28 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0
#29 0x00007ffff6516bcd in clone () from /lib64/libc.so.6- 第二步 insert into testunj1 values(16,17,'gaopeng'); 步骤2
需要做唯一性检查不通过上LOCK_ORDINARY[next_key_lock]等待，唯一检查会涉及到主键和唯一键，如果主键检查通过则会插入数据，然后检查二级唯一索引，如果唯一索引冲突，则主键插入的数据需要回滚。这里是因为每个索引是单独调用row_ins_index_entry_step上层函数进行单独插入的。
栈帧如下：
(gdb) bt
#0 lock_rec_set_nth_bit (lock=0x30580e8, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10ca5f0, index=0x7fffa89e3410, mode=258, rec_id=..., size=9)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484
#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d5a1b0, trx=0x7ffff10ca5f0, owns_trx_mutex=true, add_to_hash=true, prdt=0x0)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537
#3 0x0000000001a3fd2b in RecLock::add_to_waitq (this=0x7ffff0d5a1b0, wait_for=0x3054068, prdt=0x0)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1731
#4 0x0000000001a4091f in lock_rec_lock_slow (impl=0, mode=2, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2004
#5 0x0000000001a40c94 in lock_rec_lock (impl=false, mode=2, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2082
#6 0x0000000001a4ab0a in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, mode=LOCK_S, gap_mode=0, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6457
#7 0x0000000001aeff23 in row_ins_set_shared_rec_lock (type=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483
#8 0x0000000001af108d in row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, s_latch=false, mtr=0x7ffff0d5aec0, offsets_heap=0x7fff9c02ef08) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115
#9 0x0000000001af3440 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c02ef08, heap=0x7fff9c00e918, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034
#10 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421
#11 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470
#12 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618
#13 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760
#14 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945
#15 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283
#16 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406
#17 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020")at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344
#18 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466
#19 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881
#20 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773
#21 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121
#22 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746
#23 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901
#24 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY)at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490
#25 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021
#26 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312
#27 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190
---Type <return> to continue, or q <return> to quit---
#28 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0
#29 0x00007ffff6516bcd in clone () from /lib64/libc.so.6
这两步完成后，我们可以到LOCK_S|LOCK_ORDINARY[next_key_lock]的存在
---TRANSACTION 19508, ACTIVE 143 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140737233942272, query id 684 localhost root update
insert into testunj1 values(16,17,'gaopeng')
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 91 page no 4 n bits 72 index id2 of table ,addr is 0x3054068 `test`.`testunj1` trx id 19508 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock]) waiting(LOCK_WAIT)
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000011; asc ;;1: len 4; hex 80000011; asc ;;---TRANSACTION 19507, ACTIVE 148 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140737234208512, query id 682 localhost root
TABLE LOCK table `test`.`testunj1` trx id 19507 lock mode IX
RECORD LOCKS space id 91 page no 4 n bits 72 index id2 of table ,addr is 0x3056b48 `test`.`testunj1` trx id 19507 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000011; asc ;;1: len 4; hex 80000011; asc ;;
- 阶段2 做ROLLBACK
- 第三步 T1帮助T2做锁继承
从事物的指针0x7ffff10ca5f0可以看出是T2，如果有多个事物LOCK_GAP是兼容所以都可以继承完成，LOCK_GAP的存在只是为了LOCK_INTENTION，就是为了防止幻读。
如果做了GDB可以看到这里继承的锁：
(gdb) p lock->type_mode
$1 = 546 546 = 512+2+32= LOCK_GAP+LOCK_S+LOCK_REC 这个锁继承给了 heap 4 也就是记录 20(heir_heap_no=4, heap_no=5)
这里将LOCK_S|LOCK_GAP 继承到heap_no 4 上也就是 记录记录20上。
栈帧如下：
2018-10-11T08:15:44.292686Z 4 [Note] InnoDB: Trx(19999) is blocked!!!!!
[Switching to Thread 0x7ffff0da0700 (LWP 9278)]Breakpoint 2, lock_rec_set_nth_bit (lock=0x3058230, i=4) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
91 ut_ad(lock);
(gdb) bt
#0 lock_rec_set_nth_bit (lock=0x3058230, i=4) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10ca5f0, index=0x7fffa89e3410, mode=546, rec_id=..., size=9)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484
#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d9ada0, trx=0x7ffff10ca5f0, owns_trx_mutex=false, add_to_hash=true, prdt=0x0)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537
#3 0x0000000001a40152 in lock_rec_add_to_queue (type_mode=546, block=0x7fffea699ba0, heap_no=4, index=0x7fffa89e3410, trx=0x7ffff10ca5f0, caller_owns_trx_mutex=false)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853
#4 0x0000000001a4299b in lock_rec_inherit_to_gap (heir_block=0x7fffea699ba0, block=0x7fffea699ba0, heir_heap_no=4, heap_no=5)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2787
#5 0x0000000001a4475e in lock_update_delete (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200")at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3692
#6 0x0000000001c26418 in btr_cur_optimistic_delete_func (cursor=0x7ffff0d9b7c0, flags=0, mtr=0x7ffff0d9b2b0)at /root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:5200
#7 0x0000000001d54fe7 in row_undo_ins_remove_sec_low (mode=16386, index=0x7fffa89e3410, entry=0x7fffa89cde10, thr=0x7fffa89a23e8)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:260
#8 0x0000000001d55101 in row_undo_ins_remove_sec (index=0x7fffa89e3410, entry=0x7fffa89cde10, thr=0x7fffa89a23e8)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:295
#9 0x0000000001d555a8 in row_undo_ins_remove_sec_rec (node=0x7fffa89a25c0, thr=0x7fffa89a23e8)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:429
#10 0x0000000001d55810 in row_undo_ins (node=0x7fffa89a25c0, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:483
#11 0x0000000001b69c80 in row_undo (node=0x7fffa89a25c0, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:324
#12 0x0000000001b69dcd in row_undo_step (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:370
#13 0x0000000001abfea4 in que_thr_step (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1061
#14 0x0000000001ac00ae in que_run_threads_low (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1125
#15 0x0000000001ac0254 in que_run_threads (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1165
#16 0x0000000001bcf4dc in trx_rollback_to_savepoint_low (trx=0x7ffff10c95a0, savept=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:118
#17 0x0000000001bcf714 in trx_rollback_for_mysql_low (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:180
#18 0x0000000001bcf9b2 in trx_rollback_low (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:212
#19 0x0000000001bcfceb in trx_rollback_for_mysql (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:289
#20 0x00000000019b1c6c in innobase_rollback (hton=0x2edf1f0, thd=0x7fffa8012940, rollback_trx=true)at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:5126
#21 0x0000000000f6db24 in ha_rollback_low (thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/handler.cc:2007
#22 0x00000000018671d9 in MYSQL_BIN_LOG::rollback (this=0x2e39a40, thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/binlog.cc:2447
#23 0x0000000000f6ddba in ha_rollback_trans (thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/handler.cc:2094
#24 0x00000000016ca4d5 in trans_rollback (thd=0x7fffa8012940) at /root/softm/percona-server-5.7.22-22/sql/transaction.cc:356
#25 0x00000000015bc90a in mysql_execute_command (thd=0x7fffa8012940, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:4556
#26 0x00000000015c030e in mysql_parse (thd=0x7fffa8012940, parser_state=0x7ffff0d9f600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901
#27 0x00000000015b3ea2 in dispatch_command (thd=0x7fffa8012940, com_data=0x7ffff0d9fd70, command=COM_QUERY)at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490
#28 0x00000000015b2c2f in do_command (thd=0x7fffa8012940) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021
#29 0x00000000016fb8a8 in handle_connection (arg=0x3c52dd0) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312
#30 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190
#31 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0
#32 0x00007ffff6516bcd in clone () from /lib64/libc.so.6- T2自己做分裂了
分裂(heir_heap_no=5, heap_no=4) 可以看到这里将 记录20的type_mode=546分裂给记录17 也就是512+2+32=LOCK_GAP+LOCK_S+LOCK_REC。
栈帧如下：
[Switching to Thread 0x7ffff0d5f700 (LWP 9548)]Breakpoint 2, lock_rec_set_nth_bit (lock=0x3058230, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
91 ut_ad(lock);
(gdb) bt
#0 lock_rec_set_nth_bit (lock=0x3058230, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91
#1 0x0000000001a400fa in lock_rec_add_to_queue (type_mode=546, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, trx=0x7ffff10ca5f0, caller_owns_trx_mutex=false)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1845
#2 0x0000000001a42acf in lock_rec_inherit_to_gap_if_gap_lock (block=0x7fffea699ba0, heir_heap_no=5, heap_no=4)at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2829
#3 0x0000000001a44643 in lock_update_insert (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200")at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3659
#4 0x0000000001c219b4 in btr_cur_optimistic_insert (flags=0, cursor=0x7ffff0d5b6f0, offsets=0x7ffff0d5b7c8, heap=0x7ffff0d5a6e0, entry=0x7fff9c01aa70, rec=0x7ffff0d5b7c0, big_rec=0x7ffff0d5b7b8, n_ext=0, thr=0x7fff9c035c18, mtr=0x7ffff0d5aec0)at /root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:3346
#5 0x0000000001af3a0d in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c00e918, heap=0x7fff9c02ef08, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3166
#6 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421
#7 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470
#8 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618
#9 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760
#10 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945
#11 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283
#12 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0)at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406
#13 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020")at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344
#14 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466
#15 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881
#16 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8)at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773
#17 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121
#18 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746
#19 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901
#20 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY)at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490
#21 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021
#22 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312
#23 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190
#24 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0
#25 0x00007ffff6516bcd in clone () from /lib64/libc.so.6
(gdb)
最终形成如下的锁模式，因为记录 11,11已经不存在了因此
addr is 0x30580e8 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock])下不会有任何记录
---TRANSACTION 19999, ACTIVE 972 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140737233942272, query id 687 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testunj1` trx id 19999 lock mode IX
RECORD LOCKS space id 93 page no 4 n bits 72 index id2 of table ,addr is 0x30580e8 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock])RECORD LOCKS space id 93 page no 4 n bits 72 index id2 of table ,addr is 0x3058230 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP)
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000014; asc ;;1: len 4; hex 80000014; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000011; asc ;;1: len 4; hex 80000010; asc ;;可以看到 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP) 已经出来了。
三、断点及一些补遗
- lock_rec_has_to_wait 检测是否需要等待
- RecLock::add_to_waitq 将LOCK_T结构加入到rec hash中(等待)
- RecLock::lock_add 将LOCK_T结构加入到rec hash中
- lock_rec_set_nth_bit 设置LOCK_T位图
- row_ins_scan_sec_index_for_duplicate 二级唯一索引唯一性检查加锁函数
- lock_rec_other_has_conflicting 判断冲突->lock_rec_has_to_wait 检测是否需要等待
- lock_rec_inherit_to_gap LOCK继承函数
- lock_rec_inherit_to_gap_if_gap_lock LOCK分裂函数
- lock_rec_convert_impl_to_expl 隐含锁转换函数，比较复杂
1、即便是同一个block，不同事物(即便是同一个事物的不同锁模式)也需要新建一个LOCK_T结构，来表示一个锁，其以space id/page no为基础。其内存结构BITMAP会以位图的形式每一位代表一行数据是否上锁(0 or 1)
2、innodb锁类型只有LOCK_REC和LOCK_TABLE两种及行锁和表锁，但是可以有多种模式组合。
3、rec hash 通过space id 和 page no 构造 那么同一块的 都放到一个链表中，同时这个链表上的可能还有冲突而来的，所以每次获取的时候必然查看page no和space id 参考lock_rec_add_to_queue -> lock_rec_get_first_on_page函数。
4、每次增加一个lock_t 结构都会加入到rec hash中,这也是所谓的等待队列，加入队列就是指加入rec hash中关于本space id和page no的队列，当然最后还需要 bitmap的确认才能在page中找到这个锁的位置。
5、不同的事物对于同一行数据的上锁通常不共享一个lock_t，他们共同连接到rec hash的链表下面
6、判断某行是否上锁 需要不断循环整个链表使用heap no定位到bitmap 来进行判断。参考lock_rec_other_has_conflicting ->lock_rec_get_first。
7、对于某些标记为del flag还没有purge的记录，在某些情况下会加锁，但是会跳过判断，参考row_ins_scan_sec_index_for_duplicate ->row_ins_dupl_error_with_rec 函数。
row_ins_scan_sec_index_for_duplicate 片段：
if (cmp == 0 && !index->allow_duplicates) { //记录相等并且是唯一索引 ，还需要判断唯一的字段是否能够对上，同时要确认不是del flag的记录if (row_ins_dupl_error_with_rec(rec, entry,index, offsets)) { //这里会跳过 del flag的记录 不标记为 重复，IF逻辑不会停止，会继续到一行err = DB_DUPLICATE_KEY;thr_get_trx(thr)->error_info = index;......goto end_scan;}} else {ut_a(cmp < 0 || index->allow_duplicates);goto end_scan;}row_ins_dupl_error_with_rec 片段：
if (matched_fields < n_unique) { //需要相同的字段 否则判断为FLASEreturn(FALSE);}/* In a unique secondary index we allow equal key values if theycontain SQL NULLs */if (!dict_index_is_clust(index) && !index->nulls_equal) {for (i = 0; i < n_unique; i++) {if (dfield_is_null(dtuple_get_nth_field(entry, i))) {return(FALSE);}}}return(!rec_get_deleted_flag(rec, rec_offs_comp(offsets))); //如果相同 但是是del flag的记录则同样放回FALSE四、未分析出来的死锁
本系统没有ROLLBACK，但是 INSERT ON DUPLICATE 语句，对于 INSERT ON DUPLICATE/REPLACE语句都是做唯一性检查的时候普通语句进行报错，但是这两个语句会返回错误给上层，接着做相应的处理
- INSERT ON DUPLICATE 是调用的UPDATE接口
- REPLACE是调用的DELETE 然后 INSERT的接口
因此两类语句出现堵插入印象的地方就有两处要么是初次INSERT的时候，要么是唯一键冲突造成的二次更改上。而对于 INSERT ON DUPLICATE/REPLACE做唯一性检测的时候上的LOCK_X并非LOCK_S如下：
if (flags & BTR_NO_LOCKING_FLAG) {/* Set no locks when applying login online table rebuild. */} else if (allow_duplicates) {/* If the SQL-query will update or replaceduplicate key we will take X-lock forduplicates ( REPLACE, LOAD DATAFILE REPLACE,INSERT ON DUPLICATE KEY UPDATE). */err = row_ins_set_exclusive_rec_lock(lock_type, block, rec, index, offsets, thr); //此处需要对这条数据加NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl 可能转换} else {err = row_ins_set_shared_rec_lock(lock_type, block, rec, index, offsets, thr);//此处需要对这条数据加NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl 可能转换隐含锁}因此 INSERT ON DUPLICATE/REPLACE两个语句需要加锁的地方比较多并且流程比较复杂，分析比较麻烦。
死锁如下：
- 5.7.22
- RC隔离级别
- 行数据2000W左右
- INSERT ON DUPLICATE语句
- LOCK_GAP存在
- 间隔一键时间触发本死锁
- 无应用发起ROLLBACK
- 如果哪位大哥分析出来请赐教
CREATE TABLE `testgp` (
`id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'id',
`kdt_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' ,
`conversation_id` VARCHAR(100) NOT NULL ,
`fans_uid` VARCHAR(50) NOT NULL COMMENT 'fansUId',
`last_msg` BIGINT NOT NULL ,
`buyer_type` VARCHAR(32) NOT NULL ,
`last_receptionist_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' ,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_key` (`kdt_id`,`conversation_id`),
KEY `idx_kdt_lastmsg` (`kdt_id`,`last_msg`)
) ENGINE=InnoDB CHARSET=utf8mb4 ; 死锁记录
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-10-12 12:26:22 0x7fd70f7ff700
*** (1) TRANSACTION:
TRANSACTION 33473425185, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1465312, OS thread handle 140558885697280, query id 1628083907 10.255.201.50 courier update
INSERT INTO testgp (
`kdt_id`,
`conversation_id`,
`fans_uid`,
`last_msg`,
`buyer_type`,
`last_receptionist_id`
) VALUES (
41372282,
'41372282#mmp_6562662125#customerservice',
'mmp_6562662125',
1539318382643,
'mmp',
0
)
ON DUPLICATE KEY UPDATE last_msg = 1539318382643, buyer_type='mmp',last_receptionist_id= 0, updated_at = CURRENT_TIMESTAMP()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425185 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 0000000002774a7a; asc wJz;;
1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes);
2: len 8; hex 000000000836ffd3; asc 6 ;; *** (2) TRANSACTION:
TRANSACTION 33473425184, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 1460265, OS thread handle 140561654740736, query id 1628083905 10.255.201.50 courier update
INSERT INTO testgp (
`kdt_id`,
`conversation_id`,
`fans_uid`,
`last_msg`,
`buyer_type`,
`last_receptionist_id`
) VALUES (
41372282,
'41372282#mmp_6563932378#customerservice',
'mmp_6563932378',
1539318382633,
'mmp',
0
)
ON DUPLICATE KEY UPDATE last_msg = 1539318382633, buyer_type='mmp',last_receptionist_id= 0, updated_at = CURRENT_TIMESTAMP()
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425184 lock_mode X locks gap before rec
Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 0000000002774a7a; asc wJz;;
1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes);
2: len 8; hex 000000000836ffd3; asc 6 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425184 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 0000000002774a7a; asc wJz;;
1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes);
2: len 8; hex 000000000836ffd3; asc 6 ;; *** WE ROLL BACK TRANSACTION (1) 作者微信：gp_22389860
相关文章：
经历一次真实的XSS跨站攻击以及应付之策
这是一个线上真实的事情，黑客已经攻破网站，并主动给我们上报了问题的根源以及解决方案还是不错的。1.前端网站某处存在用户评论输入，黑客再此输出跨站脚本，下面的是从数据库查出来的2.后台管理人员如果浏览到这条数据就会触发这个…
在linux中 要删除abc目录,在 Linux 中,要删除 abc 目录及其全部内容的命令为:
【单选题】星子本地人说( )【判断题】音乐的音响,虽然不能直接传达抽象概念,但是却可以通过同构联觉的去描摹围绕着抽象概念的氛围。( )【判断题】专项耐力负荷量度是通过对糖酵解无氧代谢供能能力与非乳酸供能无氧耐力能力的监控实现的。【单选题】电动轮廓仪是根据( )原理制成…
ECHO.js 纯javascript轻量级延迟加载
演示 <!DOCTYPE html> <html lang"zh-CN"> <head> <meta charset"utf-8"> <title>简单的JavaScript图像延迟加载库Echo.js</title> <style> .demo img { width: 736px; height: 490px; background: url(images/…
SQL中的case when then else end用法
2019独角兽企业重金招聘Python工程师标准>>> Case具有两种格式。简单Case函数和Case搜索函数。 --简单Case函数 CASE sexWHEN 1 THEN 男WHEN 2 THEN 女 ELSE 其他 END --Case搜索函数 CASE WHEN sex 1 THEN 男WHEN sex 2 THEN 女 ELSE 其他 END这两种方式…
linux run文件夹,Linux下运行run文件
比如realplay.run安装方法如下chmod xrealplay.run./realplay.run然后他就会执行安装了，在过程中可能会要求你输入yes或no安装完后就可以用了,chmod实际上是加权限命令。＋x表示可以执行chmod[-cfvR][--help][--version]modefile...说明:Linux/Unix的档案…
POJ2796 Feel Good（单调栈）
题意： 给出一列数据，要求一个区间内最小值与区间内数据总和乘积最大值 要点： 还是单调栈，这次我自己写的，先做了几题比较简单的果然还是有效果的，这题也是一样，按点遍历，网上大神做的…
Solr占用CPU持续过高原因查询
线上java进程占用CPU忽高忽低，就是说一下子40%左右，一下子减下去。这台服务器只有Solr，所以估计是Solr在GC。 # jstat -gcutil 2072 2sJVM名词解释参考java内存泄漏的定位与分析 一些术语的中文解释： S0C：年轻…
通过一个案例理解 JWT
原文出自：https://www.pandashen.com JWT 简述 JWT（json web token）是为了在网络应用环境之间传递声明而基于 json 的开放标准，JWT 的声明一般被采用在身份提供者和服务器提供者间传递被认证的身份信息，以便于从资源服…
gitlab报错 fatal: index-pack failed error: RPC failed; result=18, HTTP code = 200解决方案
gitlab报错 "fatal: index-pack failed error: RPC failed; result18, HTTP code 200"，如下图这个问题网上有些人给出这样的解决方法是不行的， 所谓：git config --globalhttp.postBuffer 24288000 git config --list 最有代表的是…
（10）Spring Boot修改端口号【从零开始学Spring Boot】
Spring boot 默认端口是8080，如果想要进行更改的话，只需要修改applicatoin.properties文件，在配置文件中加入： server.port9090 常用配置： ######################################################## ###EMBEDDED SER…
linux查看文件安全权限,Linux系统下如何查看及修改文件读写权限
查看文件权限的语句：在终端输入:ls -l xxx.xxx (xxx.xxx是文件名)那么就会出现相类似的信息，主要都是这些：-rw-rw-r--一共有10位数其中： 最前面那个 - 代表的是类型中间那三个 rw- 代表的是所有者(user)然后那三个 rw- 代表的是组…
【网摘】检测 iframe 是否加载完成
var iframeSet document.getElementById("iframeSet"); //需要检测的 iframe if(iframeSet.attachEvent) {iframeSet.attachEvent("onload", function() {$("#loading").hide();}); } else {iframeSet.onload function() {$("#loading&q…
Java json转Map，转bean，转Listbean
引用jackson /** * json转Map，转bean，转List<bean> by http://blog.csdn.net/21aspnet/ * 需要jackjson jar包 */ public class JsonUtil {/*** Object转Json*/public static String ObjectToJson(Object value) {try {ObjectMapper mapper new…
JVM实用参数 GC日志
为什么80%的码农都做不了架构师？>>> 原文章地址：http://blog.panaihua.com/archives/151 GC日志是一个很重要的工具，它准确记录了每一次的GC的执行时间和执行结果，通过分析GC日志可以优化堆设置和GC设置，或…
linux 搜索so文件,Linux下查找和安装依赖的.so文件
以解决Webex在Linux下运行问题为例说明查找和安装依赖的.so文件方法：查找依赖的.so文件$ ldd $HOME/.webex/1324/*.so | grep not foundlibgtk-x11-2.0.so.0 > not foundlibgdk-x11-2.0.so.0 > not foundlibXmu.so.6 > not foundlibXtst.so.6 > not fou…
CentOS7.4下 VNC Server的搭建和客户端的连接配置
CentOS7.4下 VNC Server的搭建和客户端的连接配置 服务器版本：CentOS Linux release 7.4.1708 (Core) yum方式安装VNC server yum install tigervnc-server 启动vnc 服务初次启动服务时，按提示设置VNC Service密码；服务成功启动后会在 /root/…
Java生成html为pdf
使用这个： http://wkhtmltopdf.org/ 下载：http://download.gna.org/wkhtmltopdf/0.12/0.12.3/wkhtmltox-0.12.3_linux-generic-amd64.tar.xz 解压到/usr目录 调用这个bin /usr/wkhtmltox/bin/wkhtmltopdf需要注意如果中文不显示，显示为框框&…
GCD之信号量机制二
在前面GCD之信号量机制一中介绍了通过信号量设置并行最大线程数,依此信号量还可以防止多线程访问公有变量时数据有误，下面的代码能说明。 1.下面是不采用信号量修改公有变量的值 dispatch_group_t groupdispatch_group_create();// dispatch_semaphore_t semapho…
qtdll在linux系统运行,在QT下编写带DLL的程序
注:我的工作目录是: D:\My Documents\MyProject一.运行QtCreator1.新建工程/选择C Library 这里设计被调用的DLL下一步:然后输入类名:它会生成相应的(.h .cpp)下面一路NEXT就好了.二.1.新建一个空工程名为(MyTest) 这里设计调用DLL的主模块输入工程名后完成2.在工程文件内添…
Python 安装selenium
一、报错信息 No module named selenium 二、系统环境 操作系统：Win10 64位 Python版本：Python 3.7.0 三、安装参考 1、使用pip安装selenium pip install selenium 安装不成功 2、网上下载selenium, 地址：http://pypi.python.org/pypi/seleni…
跨域攻击XSS防御
Java的view层可以使用EL和JSTL 后端的ModelAndView增加 mv.addObject("xss", "<script>alert(\"test\")</script>"); View页面 ${xss} <c:out value"${xss}" escapeXml"true"></c:out> <c:out v…
[Core Java® for the Impatient]重载Java2
2019独角兽企业重金招聘Python工程师标准>>> Chapter 2. Object-Oriented Programming Set（Mutator Methods）方法改变对象的状态，Get（accessor methods）方法则不；Java中变量不持有对象ÿ…
linux系统与内核,[科普] Linux 的内核与 Linux 系统之间的关系
该楼层疑似违规已被系统折叠 隐藏此楼查看此楼在 FHS 协议里，有这样的规定：/bin/ 需要在单用户模式可用的必要命令(可执行文件)；面向所有用户，例如： cat、 ls、 cp。/boot/ 引导程序文件，例如： …
pynput使用简单说明
控制鼠标 1 from pynput.mouse import Button, Controller2 import time 3 4 mouse Controller()5 print(mouse.position)6 time.sleep(3)7 print(The current pointer position is {0}.format(mouse.position))8 9 10 #set pointer positon 11 mouse.position (277, 645) …
linux qt5.7下打地鼠源程序,基于QT的打地鼠游戏
【实例简介】基于QT的一个打地鼠游戏，采用随机数的方法，是地鼠产生随机序列，有得分界面，动画效果也不错，用C进行编程【实例截图】【核心代码】打地鼠└── 打地鼠├── erwei│ ├── Makefile│ ├── Makefi…
事务隔离机制原理深入分析以及MySQL不同隔离级别分场景下实验对比
这是我总结的事务的四种隔离机制，比较好理解，主要是有些地方文字游戏说不清楚很容易混淆： Read Uncommitted（读未提交）A未完，B已更新，未提交，A读到B已更新的数据，由于未…
cogs 362. [CEOI2004]锯木厂选址
★★★ 输入文件：two.in 输出文件：two.out 简单对比 时间限制：0.1 s 内存限制：32 MB 从山顶上到山底下沿着一条直线种植了n棵老树。当地的政府决定把他们砍下来。为了不浪费任何一棵木…
中小企业低成本快速建站的秘诀——模板建站
从14年至今，小乔已经给很多行业的客户做了不少网站。在跟我咨询建站的这些人当中，其实不乏一些创业初期经济比较紧张的个人/公司。这些个人/公司需要一个网站对外宣传，但又希望可以节省开支，所以他们往往会选择成本低的建站服务&a…
MySQL常用性能分析方法-profile，explain，索引
1.查版本号 无论做什么都要确认版本号，不同的版本号下会有各种差异。 >Select version();2.执行状态分析 显示哪些线程正在运行 >show processlist;下面是完整的信息3.show profile show profile默认的是关闭的，但是会话级别可以开启这个功能&…
MathType在手，公式不求人！
很多论文达人们的论文排版是相当漂亮的，页面也非常整齐美观，即使是理工类的论文，里面有很多的数学符号和公式，排版也是非常整洁，为什么达人们的公式论文能排版的这么完美，而自已却总是不得其门而入…