Description:
the tx_isolation is READ-COMMITTED,and this problem has been 5.7.16 until 5.7.20.
if the records are in the same partition of a table,a session will lock a row of data more.in different partition of a table,the problem does not exist.
How to repeat:
[root@localhost][test][11:25:40]> status;
--------------
mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 6
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.20-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /opt/mysql/data/mysql.sock
Uptime: 52 min 41 sec
Threads: 2 Questions: 63 Slow queries: 0 Opens: 119 Flush tables: 1 Open tables: 111 Queries per second avg: 0.019
--------------
NOT partition table：
CREATE TABLE test (
id int,
NAME VARCHAR (50),
create_time datetime (6),
PRIMARY KEY (id,create_time)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test';
insert into test(id,name,create_time) values(1,'aaa','2017-10-01');
insert into test(id,name,create_time) values(2,'aaa','2017-11-01');
insert into test(id,name,create_time) values(3,'aaa','2017-12-01');
insert into test(id,name,create_time) values(4,'aaa','2018-01-01');
insert into test(id,name,create_time) values(5,'aaa','2018-02-01');
// SESSION 1 on the id = 5 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=5;
// SESSION 2 on the id = 4 record to do an update operation
BEGIN;delete from test where id=4;
no effect.
partition table：
CREATE TABLE test (
id int,
NAME VARCHAR (50),
create_time datetime (6),
PRIMARY KEY (id,create_time)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test'
PARTITION BY RANGE COLUMNS(CREATE_TIME)
(PARTITION P201710 VALUES LESS THAN ('2017-10-31') ENGINE = InnoDB,
PARTITION P201711 VALUES LESS THAN ('2017-11-30') ENGINE = InnoDB,
PARTITION P201712 VALUES LESS THAN ('2017-12-31') ENGINE = InnoDB,
PARTITION P201801 VALUES LESS THAN ('2018-01-31') ENGINE = InnoDB,
PARTITION P201802 VALUES LESS THAN ('2018-02-28') ENGINE = InnoDB,
PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
insert into test(id,name,create_time) values(1,'aaa','2017-10-01');
insert into test(id,name,create_time) values(2,'aaa','2017-11-01');
insert into test(id,name,create_time) values(3,'aaa','2017-12-01');
insert into test(id,name,create_time) values(4,'aaa','2018-01-01');
insert into test(id,name,create_time) values(5,'aaa','2018-02-01');
select * from test partition(P201710);
select * from test partition(P201711);
select * from test partition(P201712);
select * from test partition(P201801);
select * from test partition(P201802);
[root@127.0.0.1][test][09:06:39]> select * from test partition(P201710);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 1 | aaa | 2017-10-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][09:06:44]> select * from test partition(P201711);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 2 | aaa | 2017-11-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][09:06:44]> select * from test partition(P201712);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 3 | aaa | 2017-12-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][09:06:44]> select * from test partition(P201801);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 4 | aaa | 2018-01-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][09:06:44]> select * from test partition(P201802);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 5 | aaa | 2018-02-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
// SESSION 1 on the id = 5 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=5;
// SESSION 2 on the id = 4 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=4;
no effect.
truncate table test;
insert into test(id,name,create_time) values(1,'aaa','2017-10-01');
insert into test(id,name,create_time) values(2,'aaa','2017-10-01');
insert into test(id,name,create_time) values(3,'aaa','2017-10-01');
insert into test(id,name,create_time) values(4,'aaa','2018-01-01');
insert into test(id,name,create_time) values(5,'aaa','2018-02-01');
select * from test partition(P201710);
select * from test partition(P201711);
select * from test partition(P201712);
select * from test partition(P201801);
select * from test partition(P201802);
[root@127.0.0.1][test][09:10:09]> select * from test partition(P201710);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 1 | aaa | 2017-10-01 00:00:00.000000 |
| 2 | aaa | 2017-10-01 00:00:00.000000 |
| 3 | aaa | 2017-10-01 00:00:00.000000 |
+----+------+----------------------------+
3 rows in set (0.00 sec)
[root@127.0.0.1][test][09:10:14]> select * from test partition(P201711);
Empty set (0.00 sec)
[root@127.0.0.1][test][09:10:14]> select * from test partition(P201712);
Empty set (0.00 sec)
[root@127.0.0.1][test][09:10:14]> select * from test partition(P201801);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 4 | aaa | 2018-01-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][09:10:14]> select * from test partition(P201802);
+----+------+----------------------------+
| id | NAME | create_time |
+----+------+----------------------------+
| 5 | aaa | 2018-02-01 00:00:00.000000 |
+----+------+----------------------------+
1 row in set (0.00 sec)
// SESSION 1 on the id = 5 and 4 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=5;delete from test where id=4;
// SESSION 2 on the id = 3、2、1 record to do an update operation
BEGIN;delete from test where id=3;delete from test where id=2;delete from test where id=1;
no effect.
// SESSION 1 on the id = 3 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=3;
// SESSION 2 on the id = 5、4、1、2 record to do an update operation
BEGIN;delete from test where id=5;delete from test where id=4;delete from test where id=1;delete from test where id=2;
effect,This data of id =2 can not get the lock,others are normal.
// SESSION 1 on the id = 3 record to do an update operation, and the transaction does not submit.
BEGIN;delete from test where id=3;
// SESSION 2 do an insert operation which id=2,and on the id = 1 record to do an update operation.
BEGIN;insert into test(id,name,create_time) values(2,'aaa','2017-10-01'); --- OK
delete from test where id=1;---stuck,failed to get the lock
effect,lock the data of id =1 ,not the range lock of 1 to 3 where id is.
Suggested fix:
no.
Description: the tx_isolation is READ-COMMITTED,and this problem has been 5.7.16 until 5.7.20. if the records are in the same partition of a table,a session will lock a row of data more.in different partition of a table,the problem does not exist. How to repeat: [root@localhost][test][11:25:40]> status; -------------- mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 6 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.20-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /opt/mysql/data/mysql.sock Uptime: 52 min 41 sec Threads: 2 Questions: 63 Slow queries: 0 Opens: 119 Flush tables: 1 Open tables: 111 Queries per second avg: 0.019 -------------- NOT partition table： CREATE TABLE test ( id int, NAME VARCHAR (50), create_time datetime (6), PRIMARY KEY (id,create_time) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test'; insert into test(id,name,create_time) values(1,'aaa','2017-10-01'); insert into test(id,name,create_time) values(2,'aaa','2017-11-01'); insert into test(id,name,create_time) values(3,'aaa','2017-12-01'); insert into test(id,name,create_time) values(4,'aaa','2018-01-01'); insert into test(id,name,create_time) values(5,'aaa','2018-02-01'); // SESSION 1 on the id = 5 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=5; // SESSION 2 on the id = 4 record to do an update operation BEGIN;delete from test where id=4; no effect. partition table： CREATE TABLE test ( id int, NAME VARCHAR (50), create_time datetime (6), PRIMARY KEY (id,create_time) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test' PARTITION BY RANGE COLUMNS(CREATE_TIME) (PARTITION P201710 VALUES LESS THAN ('2017-10-31') ENGINE = InnoDB, PARTITION P201711 VALUES LESS THAN ('2017-11-30') ENGINE = InnoDB, PARTITION P201712 VALUES LESS THAN ('2017-12-31') ENGINE = InnoDB, PARTITION P201801 VALUES LESS THAN ('2018-01-31') ENGINE = InnoDB, PARTITION P201802 VALUES LESS THAN ('2018-02-28') ENGINE = InnoDB, PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB); insert into test(id,name,create_time) values(1,'aaa','2017-10-01'); insert into test(id,name,create_time) values(2,'aaa','2017-11-01'); insert into test(id,name,create_time) values(3,'aaa','2017-12-01'); insert into test(id,name,create_time) values(4,'aaa','2018-01-01'); insert into test(id,name,create_time) values(5,'aaa','2018-02-01'); select * from test partition(P201710); select * from test partition(P201711); select * from test partition(P201712); select * from test partition(P201801); select * from test partition(P201802); [root@127.0.0.1][test][09:06:39]> select * from test partition(P201710); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 1 | aaa | 2017-10-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) [root@127.0.0.1][test][09:06:44]> select * from test partition(P201711); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 2 | aaa | 2017-11-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) [root@127.0.0.1][test][09:06:44]> select * from test partition(P201712); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 3 | aaa | 2017-12-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) [root@127.0.0.1][test][09:06:44]> select * from test partition(P201801); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 4 | aaa | 2018-01-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) [root@127.0.0.1][test][09:06:44]> select * from test partition(P201802); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 5 | aaa | 2018-02-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) // SESSION 1 on the id = 5 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=5; // SESSION 2 on the id = 4 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=4; no effect. truncate table test; insert into test(id,name,create_time) values(1,'aaa','2017-10-01'); insert into test(id,name,create_time) values(2,'aaa','2017-10-01'); insert into test(id,name,create_time) values(3,'aaa','2017-10-01'); insert into test(id,name,create_time) values(4,'aaa','2018-01-01'); insert into test(id,name,create_time) values(5,'aaa','2018-02-01'); select * from test partition(P201710); select * from test partition(P201711); select * from test partition(P201712); select * from test partition(P201801); select * from test partition(P201802); [root@127.0.0.1][test][09:10:09]> select * from test partition(P201710); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 1 | aaa | 2017-10-01 00:00:00.000000 | | 2 | aaa | 2017-10-01 00:00:00.000000 | | 3 | aaa | 2017-10-01 00:00:00.000000 | +----+------+----------------------------+ 3 rows in set (0.00 sec) [root@127.0.0.1][test][09:10:14]> select * from test partition(P201711); Empty set (0.00 sec) [root@127.0.0.1][test][09:10:14]> select * from test partition(P201712); Empty set (0.00 sec) [root@127.0.0.1][test][09:10:14]> select * from test partition(P201801); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 4 | aaa | 2018-01-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) [root@127.0.0.1][test][09:10:14]> select * from test partition(P201802); +----+------+----------------------------+ | id | NAME | create_time | +----+------+----------------------------+ | 5 | aaa | 2018-02-01 00:00:00.000000 | +----+------+----------------------------+ 1 row in set (0.00 sec) // SESSION 1 on the id = 5 and 4 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=5;delete from test where id=4; // SESSION 2 on the id = 3、2、1 record to do an update operation BEGIN;delete from test where id=3;delete from test where id=2;delete from test where id=1; no effect. // SESSION 1 on the id = 3 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=3; // SESSION 2 on the id = 5、4、1、2 record to do an update operation BEGIN;delete from test where id=5;delete from test where id=4;delete from test where id=1;delete from test where id=2; effect,This data of id =2 can not get the lock,others are normal. // SESSION 1 on the id = 3 record to do an update operation, and the transaction does not submit. BEGIN;delete from test where id=3; // SESSION 2 do an insert operation which id=2,and on the id = 1 record to do an update operation. BEGIN;insert into test(id,name,create_time) values(2,'aaa','2017-10-01'); --- OK delete from test where id=1;---stuck,failed to get the lock effect,lock the data of id =1 ,not the range lock of 1 to 3 where id is. Suggested fix: no.