如果引用的id存在，InnoDB只插入记录（没有FOREIGN KEYS）

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:

让我假装在InnoDB数据库中有两个表:类别和笑话;并且我正在使用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

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!")

这里有两个函数,每个函数同时由不同的连接调用。调用是: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;
}

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.

现在,如果两个函数的SELECT语句同时执行,并从那里继续,delete_category会认为删除类别是可以的,add_joke会认为将笑话添加到现有类别是可以的,所以我会得到空类别表和笑话表中引用不存在的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).

1)在delete_category开头的“LOCK TABLES类别WRITE,jokes WRITE”。但是,由于我正在使用InnoDB,我非常希望避免锁定整个表(特别是经常使用的主表)。

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.

2)使add_joke成为事务,然后在插入记录后执行“SELECT id FROM categories WHERE id ='$ category_id'”。如果此时不存在,则回滚事务。但是,由于add_joke中的两个SELECT语句可能返回不同的结果,我相信我需要查看事务隔离级别,这是我不熟悉的。

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.

在我看来,如果我做了这两件事,它应该按预期工作。不过,我很想听到更多知情意见。谢谢。

1 个解决方案

#1

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.

如果该类别有任何笑话,则联接将找到它们,因此外部联接将返回非空结果。 WHERE子句中的条件消除了非null结果,因此整体删除将匹配零行。

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.

如果没有这样的类别,SELECT返回零行,INSERT是无操作。

Both of these cases create a shared lock (S-lock) on the categories table.

这两种情况都会在类别表上创建共享锁(S锁)。

Demonstration of an S-lock:

演示S锁:

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.

你可以看到这会在表foo上创建一个IS锁,在foo的一行上创建一个S锁,我正在读取的表。

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.

任何混合读/写操作都会发生同样的事情,例如SELECT ... FOR UPDATE,INSERT ... SELECT,CREATE TABLE ... SELECT,阻止正在读取的行被修改而需要它们作为源写操作。

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.

IS锁定是一个表级锁定,可以防止表上的DDL操作,因此在此事务依赖于表中的某些内容时,不会发出DROP TABLE或ALTER TABLE。

#1