mysql外键读锁_你容易忽视的mysql外键锁和自增锁

一、外键锁

一般在插入大量数据的时候要先禁用外键(SET foreign_key_checks = 0)。那是为什么呢？下面我们对此进行研究：

表结构：CREATE TABLE `foreign_no1` (

`id` int(11) NOT NULL DEFAULT ‘0‘,

`no` int(11) DEFAULT NULL,

PRIMARY KEY (`id`))CREATE TABLE `foreign_no2` (

`id` int(11) NOT NULL DEFAULT ‘0‘,

`no` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `foreign_no2_ibfk_2` FOREIGN KEY (`id`) REFERENCES`foreign_no1` (`id`) ON DELETE CASCADE)

数据：mysql> select * from foreign_no1;

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

| id | no  |

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

|  1|    2 |

|  2|    3 |

+----+------+mysql> select * from foreign_no2;

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

| id | no  |

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

|  1|    5 |

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

Delete操作

子表父表

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from foreign_no2 whereid=1;

Query OK, 1 row affected (0.00 sec)mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from foreign_no1 whereid=1 lock in share mode;  ##父表可以查询

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

| id | no  |

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

|  1|    2 |

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

1 row in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from foreign_no1 whereid=1;    ##父表不允许做更改操作

^CCtrl-C -- sending "KILL QUERY78" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution wasinterrupted

mysql> rollback

可以看到子表做delete操作，父表可以做select但不能运行dml语句，因为子表对父表相关的一行记录做了读锁操作，只允许读，不能修改，以此来保障父表和子表的数据完整性。

父表

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from foreign_no1 whereid=2;

Query OK, 1 row affected (0.00 sec)

可以看到父表锁对应的一行做了读锁操作，其余记录行依然能更新。

Insert操作

子表父表

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into foreign_no2 values (3,5);

Query OK, 1 row affected (0.00 sec)mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from foreign_no1 where id=3;

^CCtrl-C -- sending "KILL QUERY78" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution wasinterrupted

mysql> update  foreign_no1 set id=8 where id=3;

^CCtrl-C -- sending "KILL QUERY78" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution wasinterrupted

可以发现insert也和delete情况一样，父表锁对应的一行做了读锁操作。

Update操作

子表父表

mysql> start transaction;

mysql> update foreign_no2 set id =3where id=2;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1 Warnings: 0mysql> delete from foreign_no1 whereid=3;

^CCtrl-C -- sending "KILL QUERY78" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution wasinterrupted

mysql> delete from foreign_no1 whereid=2;

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

可以看到被update相关的父表两条记录都加了锁。

在对子表进行操作时，外键锁将会在父表中锁住相应的记录来保证数据完整性。所以为了防止在子表更新时对父表数据锁的现象发生，一般在大量插入数据时会禁用外键约束。

二、自增锁

insert一般分为两种，

第一种：简单的insert。插入之前知道插入的行数。如：insert ,replace

第二种：块的insert。插入之前不知道多少插入的行数。如:insert select ，load data

简单insert不会生成auto-inc locking(自增锁)。那块的insert 会不会呢？

mysql> show create tableFreedom.key_id_inre;

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

| Table       | Create Table                                                                                                                                                                       |

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

| key_id_inre | CREATE TABLE `key_id_inre`(

`id` int(11) NOT NULL AUTO_INCREMENT,

`no` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1638392DEFAULT CHARSET=utf8 |

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

事务一事务二

mysql> insert into Freedom.key_id (id)select id from sanguo_te.players;mysql> insert into key_id_inre (no)values (100);

^CCtrl-C -- sending "KILL QUERY78" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution wasinterrupted

mysql> delete from foreign_no1 whereid=2;

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

auto-inc locking不会等到事务提交后才释放，而是插入好以后立即释放。当事务一，开始insert 时，事务二被锁住，当事务一insert 结束时，事务二insert被解锁并插入成功。这样的目的是Mysql通过auto-inc locking来保障块插入是连续的所以在此期间不能有新记录插入。如果列中没有自增量，auto-inc locking锁自然也不会存在。

原文：http://10574662.blog.51cto.com/10564662/1705505