Shulou(Shulou.com)05/31 Report--

This article introduces you what are the disadvantages of MYSQL foreign keys, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Some time ago, when I dealt with the TX lock problem of Xinyao, I found that the INSERT INTO was blocked because of the foreign key. Now the foreign key can not be inserted into the INSERT INTO. I will share it with you.

Test environment:

Background: MySQL version 5.6

The isolation level is RC

INNODB used by the storage engine

The tests are as follows:

View the isolation level:

Mysql > show variables like'% iso%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Create the test table tweak pri1 and insert records separately:

Mysql > create table t_pri1 (id int primary key,name varchar (20))

Query OK, 0 rows affected (0.03 sec)

Mysql > create table t_fk1 (id int primary key,name varchar (20), pid int, foreign key (pid) references t_pri1 (id))

Query OK, 0 rows affected (0.02 sec)

Mysql >

Mysql >

Mysql > insert into t_pri1 values (1)

Query OK, 1 row affected (0.01sec)

Mysql > insert into t_pri1 values (2)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_pri1 values (3 recordings hubei1')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_pri1 values (4Jing Hubei2')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_fk1 values (1 recordings wuhanqie 1)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_fk1 values (2)

Query OK, 1 row affected (0.01sec)

You can find that there is an index on the main table, and there are two indexes on the reference table t_fk1, one is the primary key, and the other is the foreign key field with an index (unlike ORACLE, ORACLE does not add automatically)

Mysql > insert into t_fk1 values (3 recordings of wuhan1)

Query OK, 1 row affected (0.00 sec)

Mysql > show index from t_fk1

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | t_fk1 | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE |

| | t_fk1 | 1 | pid | 1 | pid | A | 3 | NULL | NULL | YES | BTREE |

+- -+

2 rows in set (0.00 sec)

Mysql > show index from t_pri1

+ -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+ -+

| | t_pri1 | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE |

+ -+

1 row in set (0.00 sec)

Session 1 executed successfully but the transaction was not committed:

Mysql > begin

->

Query OK, 0 rows affected (0.00 sec)

Mysql > update t_pri1 set name='zls' where id=1

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Session 2 (execution failed, transaction rollback after timeout):

Mysql > insert into t_fk1 values (4) Zls1)

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

View the transaction information for session 1 as follows:

Mysql > select * from information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 579835

Trx_state: RUNNING

Trx_started: 2017-09-03 23:28:16

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 3

Trx_mysql_thread_id: 171

Trx_query: select * from information_schema.innodb_trx

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 2

Trx_lock_memory_bytes: 360

Trx_rows_locked: 1Mel-only one row of records is locked

Trx_rows_modified: 1

Trx_concurrency_tickets: 0

Trx_isolation_level: READ COMMITTED

Trx_unique_checks: 1

Trx_foreign_key_checks: 1

Trx_last_foreign_key_error: NULL

Trx_adaptive_hash_latched: 0

Trx_adaptive_hash_timeout: 10000

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

Check lock blocking information: 171 sessions blocked 172 sessions

Mysql > SELECT

-> r.trx_id waiting_trx_id

-> r.trx_mysql_thread_id waiting_thread

-> r.trx_query waiting_query

-> b.trx_id blocking_trx_id

-> b.trx_mysql_thread_id blocking_thread

-> b.trx_query blocking_query

-> FROM information_schema.innodb_lock_waits w

-> INNER JOIN information_schema.innodb_trx b

-> ON b.trx_id = w.blocking_trx_id

-> INNER JOIN information_schema.innodb_trx r

-> ON r.trx_id = w.requesting_trx_id

+- - - - -- +

| | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | |

+- - - - -- +

| | 579836 | 172nd | insert into t_fk1 values (4recoveryzls1) | 579835 | 579835 | SELECT |

R.trx_id waiting_trx_id

R.trx_mysql_thread_id waiting_thread

R.trx_query waiting_query

B.trx_id blocking_trx_id

B.trx_mysql_thread_id blocking_thread

B.trx_query blocking_query

FROM information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r

ON r.trx_id = w.requesting_trx_id |

+- - - - -- +

For the requested lock information, session 172 requests the number of S records in the resource: 123 _ (3) _ (2), and session 171 holds the number of X _ records in the resource: 123 _ (3) _ (2) (combined with the above query results), that is, when the record is inserted into the foreign key table, the S lock on the index value of the corresponding master table needs to be applied for. However, because the main table is currently doing the UPDATE statement based on the index value and the transaction is not committed (the same lock_space,lock_page,lock_rec represents the same lock resource):

Mysql > select * from information_schema.innodb_locks\ G

* * 1. Row *

Lock_id: 579836:123:3:2

Lock_trx_id: 579836

Lock_mode: S

Lock_type: RECORD

Lock_table: `test`.`t _ pri1`

Lock_index: PRIMARY

Lock_space: 123

Lock_page: 3

Lock_rec: 2

Lock_data: 1

* 2. Row * *

Lock_id: 579835:123:3:2

Lock_trx_id: 579835

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t _ pri1`

Lock_index: PRIMARY

Lock_space: 123

Lock_page: 3

Lock_rec: 2

Lock_data: 1

2 rows in set (0.00 sec)

Summary: MySQL, like ORACLE, does not use the primary foreign key of the database to satisfy the consistency of business logic and data. It is best to consider these at the business design level.

Liberate the database and let the database do simple DML and storage functions.

What are the disadvantages of MYSQL foreign keys to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.