|
tx_isolation is READ-COMMITTED
table:
CREATE TABLE `b` (
|
`a` int(11) NOT NULL,
|
`b` int(11) NOT NULL,
|
`c` varchar(20) COLLATE utf8_bin DEFAULT NULL,
|
`d` varchar(10) COLLATE utf8_bin DEFAULT NULL,
|
PRIMARY KEY (`a`,`b`)
|
data:
MariaDB [test]> select * from b;
|
+---+----+-------+------+
|
| a | b | c | d |
|
+---+----+-------+------+
|
| 1 | 1 | 222 | 1 |
|
| 1 | 2 | 333 | 2 |
|
| 1 | 3 | 10406 | 3 |
|
| 1 | 4 | 60736 | 4 |
|
| 1 | 5 | 50170 | 5 |
|
| 1 | 6 | 55447 | 6 |
|
| 1 | 7 | 29705 | 7 |
|
| 1 | 8 | 13657 | 8 |
|
| 1 | 9 | 63638 | 9 |
|
| 1 | 10 | 36187 | 10 |
|
+---+----+-------+------+
|
10 rows in set (0.01 sec)
|
excute plain is:
MariaDB [test]> explain update test.b set c ='111' where b= 1;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | b | index | NULL | PRIMARY | 8 | NULL | 10 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
When i opened 5 sessions to execute
update test.b set c ='111' where b=1,2,3,4,5
|
some of the sessions may lock more than one rows,like this:
---TRANSACTION 122145, ACTIVE 761 sec
|
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
|
MySQL thread id 31, OS thread handle 0x7f644b3ec700, query id 420 localhost root cleaning up
|
TABLE LOCK table `test`.`b` trx id 122145 lock mode IX
|
RECORD LOCKS space id 5 page no 3 n bits 192 index `PRIMARY` of table `test`.`b` trx id 122145 lock_mode X locks rec but not gap
|
Record lock, heap no 118 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
|
0: len 4; hex 80000001; asc ;;
|
1: len 4; hex 80000003; asc ;;
|
2: len 6; hex 00000001dd21; asc !;;
|
3: len 7; hex 170000019518c3; asc ;;
|
4: len 3; hex 313131; asc 111;;
|
5: len 1; hex 33; asc 3;;
|
|
RECORD LOCKS space id 5 page no 3 n bits 192 index `PRIMARY` of table `test`.`b` trx id 122145 lock_mode X locks rec but not gap
|
Record lock, heap no 99 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
|
0: len 4; hex 80000001; asc ;;
|
1: len 4; hex 80000005; asc ;;
|
2: len 6; hex 00000000383a; asc 8:;;
|
3: len 7; hex 420000019a0281; asc B ;;
|
4: len 5; hex 3530313730; asc 50170;;
|
5: len 1; hex 35; asc 5;;
|
|
I traced the source code, and guess this may be a bug: row_search_for_mysql() calls sel_set_rec_lock() try to lock one row
//if had to wait ：
|
case DB_LOCK_WAIT:
|
/* Never unlock rows that were part of a conflict. */
|
prebuilt->new_rec_locks = 0; //set new_rec_locks=0
|
//try semi consistent read ：
|
row_sel_build_committed_vers_for_mysql()
|
|
/*The lock was granted while we were
|
searching for the last committed version.
|
Do a normal locking read. */
|
|
//then:
|
case DB_SUCCESS:
|
if (clust_rec == NULL) {
|
/* The record did not exist in the read view */
|
ut_ad(prebuilt->select_lock_type == LOCK_NONE);
|
|
goto next_rec;
|
|
//only really did a semi consistent read, would set did_semi_consistent_read. but in my case,skipped this
|
did_semi_consistent_read = TRUE;
|
|
//so the value of did_semi_consistent_read is FLASE,and set prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT
|
|
if (UNIV_UNLIKELY(did_semi_consistent_read)) {
|
prebuilt->row_read_type = ROW_READ_DID_SEMI_CONSISTENT;
|
} else {
|
prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;
|
}
|
|
|
ha_innobase::unlock_row()
|
switch (prebuilt->row_read_type)
|
|
case ROW_READ_TRY_SEMI_CONSISTENT:
|
row_unlock_for_mysql(prebuilt, FALSE);
|
break;
|
case ROW_READ_DID_SEMI_CONSISTENT:
|
prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;
|
break;
|
|
row_unlock_for_mysql()
|
//because prebuilt->new_rec_locks was set to 0,so the locked row can't be unlock!!
|
if (prebuilt->new_rec_locks >= 1) {
|
lock_rec_unlock()
|
}
|
|