外键和锁
外键和锁
使用外键是需要付出代价的,也就是,即时检查,逐行进行,当然Oracle有个延时检查
要求每次修改时都要对另外一张表多一次select操作,使用select lock in share mode方式
意味着需要额外的锁,来确保该记录不会在事务未完成前被删除
这将导致更多的锁等待,甚至是死锁,因为关联到其他表,死锁很难被爬出
小测试
[plain] Session_A: mysql> begin; Query OK, 0 rows affected (0.00 sec) **加了X锁 mysql> delete from parent where id=5; Query OK, 1 row affected (0.01 sec) Session_B: mysql> begin; Query OK, 0 rows affected (0.00 sec) **想要加S锁,但失败! mysql> insert into child select 5,5; --blocking mysql> select * from information_schema.innodb_locks\G; *************************** 1. row *************************** lock_id: 14694:8:3:5 lock_trx_id: 14694 lock_mode: S lock_type: RECORD lock_table: `test`.`parent` lock_index: PRIMARY lock_space: 8 lock_page: 3 lock_rec: 5 lock_data: 5 *************************** 2. row *************************** lock_id: 14691:8:3:5 lock_trx_id: 14691 lock_mode: X lock_type: RECORD lock_table: `test`.`parent` lock_index: PRIMARY lock_space: 8 lock_page: 3 lock_rec: 5 lock_data: 5 2 rows in set (0.01 sec)
以下两个场景用外键还可以:
1)相关数据的同时更新或删除
2)保证两表数据的一致性
但在批量导入的时候,因为外键是逐行检查的,会非常慢,此时可以:
[plain] mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> load data... mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec)
外键若只是仅仅作为完整性约束,那么建议还是不要用了
虽然没有具体的性能测试报告,但外键约束往往是应用性能瓶颈所在
锁家族中和外键源远最深的莫属死锁
Oracle里,外键未加索引是导致死锁头号嫌疑犯
而InnoDB或MSSQL会自动加上一个索引,如果没有显示指定的话
即便你想删掉也是徒劳
如果程序是串行的,那根本不可能发生死锁,死锁只出现在并发场景,而RDBMS则是并发系统
现有两个会话,每个会话都持有另一个会话想要的资源,此时就会发生死锁
以下脚本TOM写的,用来检查Oracle里外键未加索引的字段
[plain] SELECT TABLE_NAME, CONSTRAINT_NAME, CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) || NVL2(CNAME3, ',' || CNAME3, NULL) || NVL2(CNAME4, ',' || CNAME4, NULL) || NVL2(CNAME5, ',' || CNAME5, NULL) || NVL2(CNAME6, ',' || CNAME6, NULL) || NVL2(CNAME7, ',' || CNAME7, NULL) || NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS FROM (SELECT B.TABLE_NAME, B.CONSTRAINT_NAME, MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1, MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2, MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3, MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4, MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5, MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6, MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7, MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8, COUNT(*) COL_CNT FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME, SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME, SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS) A, USER_CONSTRAINTS B WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE = 'R' GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS WHERE COL_CNT > ALL (SELECT COUNT(*) FROM USER_IND_COLUMNS I WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5, CNAME6, CNAME7, CNAME8) AND I.COLUMN_POSITION <= CONS.COL_CNT GROUP BY I.INDEX_NAME);
Oracle为什么不肯像MySQL/MSSQL一样在外键上强制加一个索引呢?
我想大概有几个原因:
① Oracle认为死锁发生的概率极低,几乎不会发生
② 外键加索引也是可能产生死锁,此时的死锁更能抓出
③ 维护多出的索引的开销没有收益来得大
④ 外键是个麻烦事,很多场景下对性能会造成伤害
⑤ 父表没有删除行的习惯
⑥ 父表没有连接子表的习惯
⑦ 父表没有更新主键/唯一键的习惯[RDBMS很少会更新主键!!]
InnoDB通常不会回滚大部分错误异常,但是除了死锁,发生死锁,InnoDB会立马回滚事务,这点需要注意
[plain] 会话A: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id=1 for update; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) 会话B: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id=2 for update; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) 会话A: mysql> select * from t where id=2 for update; --被阻塞 会话B: mysql> select * from t where id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 会话A: +----+ | id | +----+ | 2 | +----+ 1 row in set (16.69 sec)