mysql rr和rc_MySQL的在RC和RR模式下的锁

InnoDB的锁机制：

数据库使用所是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持锁的存储引擎，锁的类型有：共享锁(S)、排它锁(X)、意向共享锁(IS)、意向排它锁(IX)。为了支持更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特写：MVCC实现的。

InnoDB的锁分类：

Record Lock：行锁：单个行记录上的行锁

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身

Next-Key Lock：Gap+Record Lock，锁定一个范围，并且锁定记录本身

无索引+RC/RR

当对无索引的字段进行更新时(RR级别)，通过锁主键的方式，来锁住所有记录，RC级别不会锁所有记录。

构建表及初始化数据：

mysql -uroot -p

USE test;

DROP TABLE IF EXISTS t_none;

CREATE TABLE `t_none` (

`id` int(11) NOT NULL,

`mem_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

INSERT INTO t_none VALUES(1,1),(3,3),(5,5),(9,9),(11,11);

REPEATABLE-READ(RR)默认级别

Session A

Session B

root@localhost[zjkj]:10:53:18>prompt A>>

PROMPT set to 'A>>'

A>>select @@session.tx_isolation;

root@localhost[(none)]:11:02:58>prompt B>>

PROMPT set to 'B>>'

B>>select @@session.tx_isolation;

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_none;

+----+--------+

| id | mem_id |

+----+--------+

|  1 |      1 |

|  3 |      3 |

|  5 |      5 |

|  9 |      9 |

| 11 |     11 |

+----+--------+

5 rows in set (0.00 sec)

B>>select * from t_none;

+----+--------+

| id | mem_id |

+----+--------+

|  1 |      1 |

|  3 |      3 |

|  5 |      5 |

|  9 |      9 |

| 11 |     11 |

+----+--------+

5 rows in set (0.00 sec)

A>> select * from t_none where mem_id=3 for update;

+----+--------+

| id | mem_id |

+----+--------+

|  3 |      3 |

+----+--------+

1 row in set (0.01 sec)

B>>insert into t_none values(2,2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B>>delete from t_none where id=9;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show engin inondb status部分输出：

------------

TRANSACTIONS

------------

Trx id counter 10661

Purge done for trx's n:o < 10659 undo n:o < 0 state: running but idle

History list length 351

Total number of lock structs in row lock hash table 2

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 10588, not started

MySQL thread id 4, OS thread handle 0x7f6f5085c700, query id 339 localhost root init

show engine innodb status

---TRANSACTION 10660, ACTIVE 17 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)

MySQL thread id 11, OS thread handle 0x7f6f508de700, query id 338 localhost root update

insert into t_none values(2,2)

------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `test`.`t_none` trx id 10660 lock_mode X locks gap before rec insert intention waiting

#通过上面很容易的看到，没有通过索引for update时，当进行增删改都会锁住，MySQL内部会通过基于锁默认主键方式，对所有记录加X锁。

下面是RC级别的实验

Read Committed级别(RC)

Session A

Session B

A>>