Description:
It's a very inserting problem~~, I test it by sysbench, the table's structure like below(do not create the secondary index, it's not necessary for this case)
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
there are some records in this table, and then I start 2 sessions to run the SQL in the master:
session1:
xa start 'aaa';
delete from sbtest1 where id=123456; //id=123456 exist in the sbtest1
xa end 'aaa';
xa prepare 'aaa';
session2:
xa start 'bbb';
insert into sbtest1 values(123450,0,'','bbb');
xa end 'bbb';
xa prepare 'bbb';
after session2 is prepared, commit seesion1 & session2.
from MySQL 5.7, the xa transactions can be recorded in the binlog, so the slave can apply the operations, the binlog maybe like this:
xa start 'aaa';delete from sbtest1 where id=123456;xa end 'aaa';xa prepare 'aaa';
xa start 'bbb';insert into sbtest1 values(123450,0,'','bbb');xa end 'bbb';xa prepare 'bbb'
xa commit 'aaa'
xa commit 'bbb'
In my case, the replication is broken when slave apply the binlog(relaylog), it says: Lock wait timeout exceeded.
two SQL can be executed on the master, but on slave, there're conflicted, why?
when session2 execute on slave, it add the insert intention lock, and the session1 add the X lock when the record(id=123456) exist, so i test slave & master like bellow:
sessionA: begin; select * from sbtest1 where id=123456 for update
sessionB: insert insert into sbtest1 values(123450,0,'','bbb');
on the slave, sessionA is blocked by sessionB, but on master not, why? all the records on master & slave are the same。
the data beside the record(id=123456) like this: (...),(123440,0,'','xxx'),(123456,0,'','xxx'),(123460,0,'','xxxx),(...)
the record with the id=123450 is between (123440, 123456) , and the sessionB will add the insert intention lock on(123440, 123456), and the sessionA add the X Lock on id=123456, these two locks are not conflicted!!! But what happens on slave?
when execute sessionA & sessionB on slave, I do "select * from information_schema.INNODB_LOCKS" use another session, I shows that:
mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| 2388:29:2772:1 | 2388 | X | RECORD | `mydb`.`sbtest1` | PRIMARY | 29 | 2772 | 1 | supremum pseudo-record |
| 2387:29:2772:1 | 2387 | X | RECORD | `mydb`.`sbtest1` | PRIMARY | 29 | 2772 | 1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
the two sessions are locked on "supremum pseudo-record" in the same time.
debugged slave(row0sel.cc::row_search_mvcc), I got that: when a record exist on the page for the first one, if want to add the X Lock on it, the supremum of pre-page will be add X Lock(maybe next key lock, supremum can be add gap lock only) also.
summary:
two pages with records like these: (..., 123440, supremum),(infimum, 123456, ...), when we want to add the X lock on record(id=123456), first, find the pre-record(id=123440), then add the X Lock on any records after this record until the record with id=123456, so in my case , the supremum and id=123456 are locked (infimum can not be locked).
all doubts are clearly, In my case on slave
(..., 123440, supremum),(infimum, 123456, ...), supremum & 123456 is locked by X Lock when session1 execute, session2 want to add insert intention lock on the gap(123440, 123456), the supremum is between this gap, insert intention lock is blocked.
on the master , the record(id=123456) maybe not the first record on the page, so the two sessions not conflicted.
the end, in my case the session1 lock the supremum is necessary?
How to repeat:
such as I described, I constructed a case that add the X lock on the first record of a page.
how to repeat:
1. create a table with primary key(id auto increment is better)
2. insert some records in this table
3. use 3 sessions to find the first record on one page(perhaps, there are many pages, any one is OK).how to?
session1: begin; select * from table_name where id=xxx for update // id=xxx must be exist
session2: select * from table_name where id=xxx for update
session3: when session2 is blocked, execute: show engine innodb status\G;
in the transaction section, you can find some details like this:
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 2772 n bits 144 index PRIMARY of table `mydb`.`sbtest1` trx id 2388 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
from the information "space id 29 page no 2772" can find the page of this record.
then test for next id until the page no become to 2773, remember this id, this record is the first one of page 2773
4. delete some records before this id witch get in step 3
5. use 2 sessions to do what i described
I repeat it by sysbench(version 1.0).
1. use sysbench to create a table with 500,000 records: sysbench --table-size=500000 --tables=1 --threads=64 --create_secondary=false --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-db=mydb oltp_read_write.lua prepare
2. delete the records between(200000, 200057), delete from sbtest1 where id>200000 and id < 200057
3. two sessions, session1: begin;select * from sbtest1 where id=200057 for update; session2: insert into sbtest1 values(200050, 0,'a','a')
session2 will be blocked by session1.
Because this problem is related to the position of the record in the page, repeat it like my operations, do not use the compressed table and make sure the innodb_page_size = 16384
Suggested fix:
I'm not sure the lock on supremum is necessary in my case ? this lock resulting the difference even if the two instances have the same records.
