1

I have two transactions. One that assign tags to post after it's creation (Transaction B). Another transaction (Transaction A) deletes tags which are no longer used by any post. It's being done after post deletion.

Transaction A

SELECT COUNT(*) FROM post_tags WHERE tag_id = 45;

Transaction B

SELECT id FROM tag WHERE name = 'rss';

Transaction A

DELETE FROM tag WHERE id = 45;

Transaction B

-- Now, foreign key violation
INSERT INTO post_tags(post_id, tag_id) VALUES(123, 45);

I know I can use SELECT FOR UPDATE in both transaction, but I don't want to lock transactions which just assign tags. I want them to lock only if there is a transaction which is trying to delete the tag.

Ivan Virabyan
  • 1,666
  • 2
  • 19
  • 25
  • use repeatable read transaction isolation level.....it may help u – vidyadhar Mar 27 '13 at 12:49
  • Is Transaction A's purpose deleting tags without posts? If yes - when does it run (on some event or a cron job)? – Vatev Mar 27 '13 at 13:41
  • @Vatev yes, it does delete tags without posts. It runs after post remove (I can make it run on a cron job if it solves the problem) – Ivan Virabyan Mar 28 '13 at 08:17
  • Why don't you use ON DELETE CASCADE as described here?: http://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql – starshine531 Mar 28 '13 at 09:17

0 Answers0