MYSQL之Innodb锁

MYSQL之Innodb锁  

Innodb的有行锁和表锁之分，这里摘录一些行锁的特性：

何为共享锁、排他锁、以及意向共享锁、意向排他锁（意向是针对多行的锁）

 本文的重点是下面这点：

Innodb的行锁是根据检索的索引项进行加锁（注意不要理解成对索引加锁），具体的说是对所有根据索引检索到的记录进行加锁（由于Innodb使用主键为聚簇索引的方式，也可以理解为对主键进行加锁，Innodb总会有聚簇索引的，即使没有也会隐式生成一个），这样就决定了Innodb的行锁会有下面一些性质：

• 只有通过索引检索，才能使用行锁，否则使用表锁
• 使用相同索引键来访问即使是不同行记录，也会出现锁冲突。（这里指的是需要锁的情况下。在非一致性锁定读情况下，即使读取的行已经被使用Select ... For Update，也是可以读的（因为读的是快照））
• 间隙锁，范围检索以及对不存在记录相等条件的检索都会使用，所以应该在设计上尽量避免，避免导致严重的锁等待

以下情况会使用表锁：

• 没有使用索引进行检索
• 判断不同执行计划的代价，如果全表扫描效率更高，也会使用表锁
• 数据类型转化而导致表锁。如下会对name进行类型转化，而执行全表扫描

使用相同索引键检索即使不同记录，也导致锁等待的例子：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> desc my_lock;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  | MUL | NULL    |       |
| b     | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select *from my_lock;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> explain select *from my_lock where a=1 and b=1 for update; //使用了索引a进行检索以更新
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | my_lock | ref  | a,b           | a    | 5       | const |    1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> select *from my_lock where a=1 and b=1 for update;//虽然只检索到一条记录，但对所有a=1的记录锁定
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

// 这里启动另外一个session，检索另外一条记录以更新，将阻塞，直到session1 -> commit

// mysql> select *from my_lock where a=1 and b=2 for update; -> 阻塞中，直到session1执行下面语句

// mysql> select *from my_lock where a=1 and b=2; -> ok 不会阻塞，对于一致性非锁定读（读取的是快照，对应事务隔离等级2,3）

mysql> commit; //提交解除锁
Query OK, 0 rows affected (0.00 sec)

 

数据类型转化导致全表扫描的例子：

mysql> alter table tab_no_index add index name(name);

Query OK, 4 rows affected (8.06 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from tab_with_index where name = 1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ALL

possible_keys: name

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from tab_with_index where name = '1' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ref

possible_keys: name

          key: name

      key_len: 23

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)