InnoDB仅在引用的ID存在时插入记录(不包含FOREIGN KEYS) [英] InnoDB only insert record if referenced id exists (without FOREIGN KEYS)
问题描述
外键可能是解决此问题的最佳方法.但是，我试图了解表锁定/事务，因此希望我们暂时可以忽略它们.
假设我在 InnoDB 数据库中有两个表:categories和jokes;并且我正在使用 PHP/MySQLi 来完成工作.表格看起来像这样:
CATEGORIES
id (int, primary, auto_inc) | category_name (varchar[64])
============================================================
1 knock, knock
JOKES
id (int, primary, auto_inc) | category_id (int) | joke_text (varchar[255])
=============================================================================
empty
这里有两个函数，每个函数同时被不同的连接调用.调用是:delete_category(1)和add_joke(1,"Interrupting cow. Interrup-MOOOOOOOO!")
function delete_category($category_id) {
// only delete the category if there are no jokes in it
$query = "SELECT id FROM jokes WHERE category_id = '$category_id'";
$result = $conn->query($query);
if ( !$result->num_rows ) {
$query = "DELETE FROM categories WHERE id = '$category_id'";
$result = $conn->query($query);
if ( $conn->affected_rows ) {
return true;
}
}
return false;
}
function add_joke($category_id,$joke_text) {
$new_id = -1;
// only add the joke if the category exists
$query = "SELECT id FROM categories WHERE id = '$category_id'";
$result = $conn->query($query);
if ( $result->num_rows ) {
$query = "INSERT INTO jokes (joke_text) VALUES ('$joke_text')";
$result = $conn->query($query);
if ( $conn->affected_rows ) {
$new_id = $conn->insert_id;
return $new_id;
}
}
return $new_id;
}
现在，如果两个函数中的SELECT语句同时执行并从那里继续执行，则delete_category认为可以删除类别，而add_joke则可以将笑话添加到现有类别，因此我将获得一个空的categories表和joke表中的一个条目，该条目引用了不存在的category_id.
不使用外键，您将如何解决此问题?
到目前为止，我最好的想法是执行以下操作:
1)"LOCK TABLES categories WRITE, jokes WRITE"在delete_category的开头.但是，由于我使用的是InnoDB，因此我非常希望避免锁定整个表(尤其是经常使用的主表).
2)使add_joke成为事务，然后在插入记录后也进行"SELECT id FROM categories WHERE id = '$category_id'" .如果那时还不存在，请回滚事务.但是，由于add_joke中的两个SELECT语句可能返回不同的结果，所以我认为我需要研究我不熟悉的事务隔离级别.
在我看来，如果我同时完成了这两项工作，那么它应该会按预期工作.不过，我很想听听更多的见解.谢谢.
仅当没有匹配的笑话时，您才能删除类别:
DELETE c FROM categories AS c
LEFT OUTER JOIN jokes AS j ON c.id=j.category_id
WHERE c.id = $category_id AND j.category_id IS NULL;
如果该类别有任何笑话，则联接将找到它们，因此外部联接将返回非空结果. WHERE子句中的条件消除了非空结果，因此总体删除将匹配零行.
同样，只有在类别存在的情况下，您才可以向类别插入笑话:
INSERT INTO jokes (category_id, joke_text)
SELECT c.id, '$joke_text'
FROM categories AS c WHERE c.id = $category_id;
如果没有这样的类别，则SELECT返回零行，而INSERT是空操作.
这两种情况都在类别表上创建了共享锁(S锁).
S锁的演示:
在一个会话中，我运行:
mysql> INSERT INTO bar (i) SELECT SLEEP(600) FROM foo;
在第二个会话中，我运行:
mysql> SHOW ENGINE INNODB STATUS\G
. . .
---TRANSACTION 3849, ACTIVE 1 sec
mysql tables in use 2, locked 2
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 18, OS thread handle 0x7faefe7d1700, query id 203 192.168.56.1 root User sleep
insert into bar (i) select sleep(600) from foo
TABLE LOCK table `test`.`foo` trx id 3849 lock mode IS
RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`foo` trx id 3849 lock mode S
您会看到这会在表foo上创建一个IS锁，并在foo的一行(我正在读取的表)上创建S锁.
对于任何混合读/写操作(例如SELECT...FOR UPDATE，INSERT...SELECT，CREATE TABLE...SELECT)，当需要将它们作为写操作的源时，阻止正在读取的行被修改时，也会发生同样的事情./p>
IS锁是一种表级锁，可防止对表执行DDL操作，因此在此事务依赖于表中的某些内容时，没有人发出DROP TABLE或ALTER TABLE.
Foreign keys may be the best approach for this problem. However, I'm trying to learn about table locking/transactions, and so I'm hoping that we can ignore them for the moment.
Let's pretend that I have two tables in an InnoDB database: categories and jokes; and that I'm using PHP/MySQLi to do the work. The tables look like so:
CATEGORIES
id (int, primary, auto_inc) | category_name (varchar[64])
============================================================
1 knock, knock
JOKES
id (int, primary, auto_inc) | category_id (int) | joke_text (varchar[255])
=============================================================================
empty
Here are two functions, each of which is being called by a different connection, at the same time. The calls are: delete_category(1) and add_joke(1,"Interrupting cow. Interrup-MOOOOOOOO!")
function delete_category($category_id) {
// only delete the category if there are no jokes in it
$query = "SELECT id FROM jokes WHERE category_id = '$category_id'";
$result = $conn->query($query);
if ( !$result->num_rows ) {
$query = "DELETE FROM categories WHERE id = '$category_id'";
$result = $conn->query($query);
if ( $conn->affected_rows ) {
return true;
}
}
return false;
}
function add_joke($category_id,$joke_text) {
$new_id = -1;
// only add the joke if the category exists
$query = "SELECT id FROM categories WHERE id = '$category_id'";
$result = $conn->query($query);
if ( $result->num_rows ) {
$query = "INSERT INTO jokes (joke_text) VALUES ('$joke_text')";
$result = $conn->query($query);
if ( $conn->affected_rows ) {
$new_id = $conn->insert_id;
return $new_id;
}
}
return $new_id;
}
Now, if the SELECT statements from both functions execute at the same time, and proceed from there, delete_category will think it's okay to delete the category, and add_joke will think it's okay to add the joke to the existing category, so I'll get an empty categories table and an entry in the joke table that references a non-existent category_id.
Without using foreign keys, how would you solve this problem?
My best thought so far would be to do the following:
1) "LOCK TABLES categories WRITE, jokes WRITE" at the start of delete_category. However, since I'm using InnoDB, I'm quite keen to avoid locking entire tables (especially main ones that will be used often).
2) Making add_joke a transaction and then doing "SELECT id FROM categories WHERE id = '$category_id'" after inserting the record as well. If it doesn't exist at that point, rollback the transaction. However, since the two SELECT statements in add_joke might return different results, I believe I need to look into transaction isolation levels, which I'm not familiar with.
It seems to me that if I did both of those things, it should work as expected. Nevertheless, I'm keen to hear more informed opinions. Thanks.
You can DELETE a category only if is no matching joke:
DELETE c FROM categories AS c
LEFT OUTER JOIN jokes AS j ON c.id=j.category_id
WHERE c.id = $category_id AND j.category_id IS NULL;
If there are any jokes for the category, the join will find them, and therefore the outer join will return a non-null result. The condition in the WHERE clause eliminates non-null results, so the overall delete will match zero rows.
Likewise, you can INSERT a joke to a category only if the category exists:
INSERT INTO jokes (category_id, joke_text)
SELECT c.id, '$joke_text'
FROM categories AS c WHERE c.id = $category_id;
If there is no such category, the SELECT returns zero rows, and the INSERT is a no-op.
Both of these cases create a shared lock (S-lock) on the categories table.
Demonstration of an S-lock:
In one session I run:
mysql> INSERT INTO bar (i) SELECT SLEEP(600) FROM foo;
In second session I run:
mysql> SHOW ENGINE INNODB STATUS\G
. . .
---TRANSACTION 3849, ACTIVE 1 sec
mysql tables in use 2, locked 2
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 18, OS thread handle 0x7faefe7d1700, query id 203 192.168.56.1 root User sleep
insert into bar (i) select sleep(600) from foo
TABLE LOCK table `test`.`foo` trx id 3849 lock mode IS
RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`foo` trx id 3849 lock mode S
You can see that this creates an IS-lock on the table foo, and an S-lock on one row of foo, the table I'm reading from.
The same thing happens for any hybrid read/write operations such as SELECT...FOR UPDATE, INSERT...SELECT, CREATE TABLE...SELECT, to block the rows being read from being modified while they are needed as a source for the write operation.
The IS-lock is a table-level lock that prevents DDL operations on the table, so no one issues DROP TABLE or ALTER TABLE while this transaction is depending on some content in the table.
这篇关于InnoDB仅在引用的ID存在时插入记录(不包含FOREIGN KEYS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
