Let's assume that we have the following tables on an InnoDB database:

  • user (id_user, name)
  • log (id_log, id_user, info)
  • message (id_message, id_user_from, id_user_to, message)

The id_user column on log and id_user_from and id_user_to on message tables are FK to user(id_user).

As I understand it, when we make an insert on log table mysql will create a shared lock on the related record on user, if exists. If another transaction try to get a shared lock, it will works fine. If another transaction try to get a exclusive lock, it will have to wait until the first transaction finishes.

My question is: If another transaction try to make an insert or update on message referencing the same record on user it will be impacted by the insert on log? I don't think so, because this transaction will get a shared lock on the record too, but I read on some old articles (from 10 years ago) that this can be an issue. If it is a case, isolation level has some impact on this? Again, I don't think so.

| improve this question | | | | |

My attitudes in this area:

  • Make transactions so fast that there are rarely any conflicts.
  • Use SELECT ... FOR UPDATE to "lock" rows that I am about to update. (Unless there is a way to avoid them.)
  • FKs imply extra checks. These take time. And the checks are usually redundant.
  • I don't use FK CASCADE since I like to see all the interactions in the same code -- namely the code between BEGIN and COMMIT.
  • I don't use non-cascading FKs; I debug my code instead.
  • I do use INDEXes and I want to know exactly what indexes exist and not be surprised by extra (redundant?) ones created by FKs.
  • Do check for errors after every SQL statement. These can be surprise deadlocks.
  • Check for errors even after COMMIT. This is in case the app is ever migrated to Galera or InnoDB Cluster.
  • Restart at the BEGIN if there is a deadlock.
  • Beware of "burned" AUTO_INCREMENT ids. (That's a lengthy discussion of its own.)

Back to your question.

There are cases where InnoDB takes a stronger (eg, X vs S) lock than it needs to. This may be because of an edge case that is too costly to check for. Your example may (or may not) be an example of such -- I don't have enough brain cells think it through at the moment. I go back to my first bullet item -- make the transaction fast enough so the problem goes away.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.