一、外键锁
一般在插入大量数据的时候要先禁用外键（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: 0 | mysql> 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
原文：http://10574662.blog.51cto.com/10564662/1705505