I use sysbench to do some tests on MySQL 5.7.x, and then I got
some problems with the lock added on supremum, I hope someone can give me some
advice.
my table like this:
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;
secondary index is not neccessary, I do not create it!!
I have two sessions:
session1: begin; select * from sbtest1 where id=123456 for update
session2: insert into sbtest1 values(123450, 0,'aaa','bbb');
the records in the table like below:
(...),(123440, 264454, xxxxxx, xxxxx), (123456, 251028, xxxxx,
xxxxxx),(....)
in my opinion, the record with the id=123456 existed, so
session1 add the X lock on this record, session2 add the insert
intention lock between(123440, 123456), these two locks are not
conflicted. session2 will not be blocked by session1.
unfortunately, in my case, session2 is blocked by session1,
what happens?
when session2 is blocked, I do "select * from
information_schema.INNODB_LOCKS" use another session, It shows :
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
|
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
both the two sessions want to add X lock on "supremum
pseudo-record" in the same time.
then I have debugged this instance(row0sel.cc::row_search_mvcc), I got
that: when a record existed on the page for the first one, if you 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.
In my case, the records' primary key stored like this:
(..., 123440, supremum), (infimum, 123456, ...)， the id=123456 is the
first record of that page, when add X Lock, first, find the pre-record
id=123440, and then add the X lock after this record(id=123440) until match the
record witch id=123456, so the supremum is locked also...， because of this
lock(supremum)， session2 is blocked. if id=123456 is not the first
record of page, session2 will not be blocked.
so my doubt is: in my case, the session1 lock the
supremum is necessary? if it's necessary, the lock on supremum maybe
resulting the difference even if two mysql instances have the same
records, just as in a replication.
the record located on the page not all the same even if two instances have
the same table sturcture & data(if slave define the table compressed, or
innodb page_size not set as the same as master, etc..). if lock on
supremum is neccessary, two instances execute the same SQLs, master is OK,
but slave conflicted? on master， the two transactions may be committed in one
group, but on slave maybe conflicted, slave's parallell replication
is OK? also in MySQL 5.7.x, the xa prepare recorded in binlog,
if on the master, execute two xa transactions:
xa start 'aaa'; delete from table_name where id=xxxx; xa end 'aaa';xa
prepare 'aaa';
xa start 'bbb';insert into table_name values(xxxx); end 'bbb'; xa prepare
'bbb';
xa commit 'aaa'; xa commit 'bbb'
on the master, these xa not conflicted, I can committed these
transaction, but on the slave, maybe conflicted
sometimes, the replication is broken.
how to repeat:
- create a table with primary key(id auto_increment is better)
- insert some records in this table
- 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",
we 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
- delete some records before this id witch get in step 3
- 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 localtion 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.
in my case, the Lock on supremum is neccessary?