Foreign key and lock

The use of foreign keys and locks requires a price, that is, instant checks and row-by-row operations, of course, Oracle has a latency check, which requires that the select Operation be performed on another table each time it is modified. Using the select lock in share mode means that additional locks are required, to ensure that the record will not be deleted before the transaction is completed. This will lead to more lock waits, or even deadlocks. Because it is associated with other tables, it is difficult to roll out the deadlock for a small test.

[Plain] Session_A: mysql> begin; Query OK, 0 rows affected (0.00 sec) ** added the X lock 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) ** you want to apply the S lock, but it failed! 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. 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)

 

In the following two scenarios, foreign keys can also be used: 1) related data is updated or deleted at the same time; 2) data consistency between the two tables is ensured, but during batch import, because the foreign key is checked row by row, it will be very slow. In this case, you can:

[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)  

 

If the foreign key is only used as the Integrity Constraint, we recommend that you do not use it. Although there is no specific performance test report, however, foreign key constraints are often the lock family in which the application performance bottleneck is located and the far deepest foreign key source is in the deadlock Oracle, the unindexed foreign key is the leading suspect in the deadlock, while InnoDB or MSSQL will automatically add an index. If the index is not displayed, even if you want to delete it, it will be futile if the program is serial, it is impossible to have a deadlock. A deadlock only occurs in a concurrent scenario, while RDBMS is the current two sessions of the concurrent system. Each session holds the resources required by another session, in this case, a deadlock occurs. The following script is written by TOM to check the fields with the Oracle internal and external keys not indexed.

[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);  

 

Why Does Oracle refuse to force an index on the foreign key like MySQL/MSSQL? I think there are several possible reasons: ① Oracle believes that the deadlock probability is extremely low, and almost no ② foreign key and index addition may also lead to deadlocks, at this time, deadlocks can better capture ③ the cost of maintaining more indexes is not more favorable. ④ Foreign keys are troublesome, in many scenarios, the performance will be compromised. ⑤ the parent table does not have the habit of deleting rows. 6 The parent table does not have the habit of connecting to the child table. 7 The parent table does not have the habit of updating the primary key/unique key. [RDBMS rarely updates. primary Key!] InnoDB usually does not roll back most errors, but in addition to deadlocks and deadlocks, InnoDB will immediately roll back the transaction.

[Plain] session 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) Session 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) session: mysql> select * from t where id = 2 for update; -- blocked session B: mysql> select * from t where id = 1 for update; ERROR 1213 (40001 ): deadlock found when trying to get lock; try restarting transaction session A: + ---- + | id | + ---- + | 2 | + ---- + 1 row in set (16.69 sec)