InnoDB only inserts a record if a link identifier exists (without IDs)

Question

Foreign keys may be the best approach for this problem. However, I am trying to find out about the table lock / transaction, and therefore I hope that we can ignore them for now.

Suppose I have two tables in an InnoDB database: categories and jokes ; and that I use PHP / MySQLi to do the job. The tables look like this:

 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 calls a different connection, at the same time . Calls: 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 both functions are executed at the same time, and proceed from there, delete_category will think that it will be possible to delete the category, and add_joke will think that it is normal to add a joke to the existing category, so I will get an empty categories table and an entry in the joke table , which refers to a category_id that does not exist.

Without using foreign keys, how would you solve this problem?

My best thought would be to do the following:

1) "LOCK TABLES categories WRITE, jokes WRITE" at the beginning of delete_category . However, since I use InnoDB, I really want to avoid locking entire tables (especially the main ones, which will be used often).

2) Executing the add_joke transaction, and then executing "SELECT id FROM categories WHERE id = '$category_id'" after inserting the record. If it does not exist at this point, the transaction is rolled back. However, since the two SELECT in add_joke may return different results, I believe that I need to study transaction isolation levels that I am not familiar with.

It seems to me that if I did both of these things, it would work as expected. However, I want to hear more informed opinions. Thanks.

Answer 1

You can DELETE a category only if it doesn't match the 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-zero result. The condition in the WHERE clause excludes nonzero results, so the total deletion will match zero rows.

Similarly, you can INSERT a joke into a category only if there is a category:

 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, SELECT returns null strings, and INSERT returns no-op.

Both of these cases create a common S-lock table in the category table.

S-lock demo:

In one session, I ran:

 mysql> INSERT INTO bar (i) SELECT SLEEP(600) FROM foo; 

In the 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 in table foo and S-lock on the same line foo, the table I am reading.

The same thing happens for any hybrid read / write operations, such as SELECT...FOR UPDATE , INSERT...SELECT , CREATE TABLE...SELECT , to block read lines due to their changing when they are needed as a source for a write operation.

IS-lock is a table-level lock that prevents DDL operations in the table, so no one issues a DROP TABLE or ALTER TABLE , while this transaction depends on some content in the table.