mysql外键读锁_外键和锁 - 923723914 - ITeye博客

使用外键是需要付出代价的，也就是，即时检查，逐行进行，当然Oracle有个延时检查

要求每次修改时都要对另外一张表多一次select操作，使用select lock in share mode方式

意味着需要额外的锁，来确保该记录不会在事务未完成前被删除

这将导致更多的锁等待，甚至是死锁，因为关联到其他表，死锁很难被爬出

小测试

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)保证两表数据的一致性

但在批量导入的时候，因为外键是逐行检查的，会非常慢，此时可以：

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里外键未加索引的字段

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会立马回滚事务，这点需要注意

会话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)

By DBA_WaterBin

2013-09-08

good luck

分享到：

2013-09-08 16:11

浏览 267

评论