外键可能是解决此问题的最佳方法。然而,我正在尝试了解表锁定/事务,因此我希望我们暂时可以忽略它们
让我们假设我在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"
在CCD_。然而,由于我使用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;
如果该类别有任何笑话,联接将找到它们,因此外部联接将返回非null结果。WHERE子句中的条件消除了非null结果,因此整体删除将匹配零行。
同样,只有当类别存在时,才能将笑话插入到该类别中:
INSERT INTO jokes (category_id, joke_text)
SELECT c.id, '$joke_text'
FROM categories AS c WHERE c.id = $category_id;
如果没有这样的类别,SELECT将返回零行,INSERT则为no-op。
这两种情况都会在类别表上创建一个共享锁(S-lock)。
S锁的演示:
在我运行的一个会话中:
mysql> INSERT INTO bar (i) SELECT SLEEP(600) FROM foo;
在第二个会话中,我运行:
mysql> SHOW ENGINE INNODB STATUSG
. . .
---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
,都会发生同样的情况,以阻止正在读取的行在需要它们作为写操作的源时被修改。
IS锁是一个表级锁,用于防止对表执行DDL操作,因此当此事务依赖于表中的某些内容时,没有人发出DROP TABLE
或ALTER TABLE
。