Description:
mysql> show variables like '%iso%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
session one:
mysql> begin;
mysql> delete from bai_test where code='b' and createtime>='2018-01-08 00:00:00';
Query OK, 200 rows affected (0.17 sec)
session two:
mysql> begin;
mysql> delete from bai_test where code='a' and createtime>='2018-01-08 00:00:00';
session two is blocked by session one, there is the lock information:
*************************** 1. row ***************************
waiting_trx_id: 15077777
waiting_thread: 2273
waiting_query: delete from bai_test
waiting_for_lock: RECORD X
blocking_trx_id: 15077772
blocking_thread: 72
blocking_query: NULL
blocking_lock: RECORD X
1 row in set (0.00 sec)
the sql used the unique index:
mysql> explain delete from bai_test where code='b' and createtime>='2018-01-08 00:00:00';
+----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+
| 1 | SIMPLE | bai_test | range | idx_code_date | idx_code_date | 127 | const,const | 200 | Using where |
+----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
session two delete different rows and should not be blocked by session one.
How to repeat:
CREATE TABLE `bai_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(40) NOT NULL,
`createtime` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_code_date` (`code`,`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=2601 DEFAULT CHARSET=utf8
this script generates test data,i run this script on 2018-01-08:
#!/bin/bash
i=1;
MAX_INSERT_ROW_COUNT=$1;
d=$(date "+%Y%m%d")
while [ $i -le $MAX_INSERT_ROW_COUNT ]
do
for c in {a..m}
do
/usr/local/mysql-5.6.25/bin/mysql -uroot -S /tmp/mysql_3333.sock -pxxx test -e "insert into bai_test (code,createtime) values ('$c','$d');"
done
d=`date -d "+$i day" +%Y-%m-%d`
i=$(($i+1))
done
exit 0
Description: mysql> show variables like '%iso%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ session one: mysql> begin; mysql> delete from bai_test where code='b' and createtime>='2018-01-08 00:00:00'; Query OK, 200 rows affected (0.17 sec) session two: mysql> begin; mysql> delete from bai_test where code='a' and createtime>='2018-01-08 00:00:00'; session two is blocked by session one, there is the lock information: *************************** 1. row *************************** waiting_trx_id: 15077777 waiting_thread: 2273 waiting_query: delete from bai_test waiting_for_lock: RECORD X blocking_trx_id: 15077772 blocking_thread: 72 blocking_query: NULL blocking_lock: RECORD X 1 row in set (0.00 sec) the sql used the unique index: mysql> explain delete from bai_test where code='b' and createtime>='2018-01-08 00:00:00'; +----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+ | 1 | SIMPLE | bai_test | range | idx_code_date | idx_code_date | 127 | const,const | 200 | Using where | +----+-------------+----------+-------+---------------+---------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) session two delete different rows and should not be blocked by session one. How to repeat: CREATE TABLE `bai_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(40) NOT NULL, `createtime` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_code_date` (`code`,`createtime`) ) ENGINE=InnoDB AUTO_INCREMENT=2601 DEFAULT CHARSET=utf8 this script generates test data,i run this script on 2018-01-08: #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; d=$(date "+%Y%m%d") while [ $i -le $MAX_INSERT_ROW_COUNT ] do for c in {a..m} do /usr/local/mysql-5.6.25/bin/mysql -uroot -S /tmp/mysql_3333.sock -pxxx test -e "insert into bai_test (code,createtime) values ('$c','$d');" done d=`date -d "+$i day" +%Y-%m-%d` i=$(($i+1)) done exit 0