1

In a web application, using the InnoDB storage engine, I was unable to adequately utilise database locking in the following scenario.

There are 3 tables, I will call them aa, ar and ai.

aa holds the base records, let's say articles. ar holds information related to each aa record and the relation between aa and ar is 1:m.

Records in ar are stored when a record from aa is read the first time. The problem is that when two requests are initiated at (nearly) the same to read a record from aa (which does not yet have its related records stored in ar), the ar records are duplicated.

Here is a pseudo code to help understand the situation:

  • Read the requested aa record.

  • Scan the ar table to find out if the given aa record has anything stored already. (Assume it has not.)

  • Consult ai in order to find out what is to be stored in ar for the given aa record. (ai seems somewhat irrelevant, but I found that it too has to be involved in the locking… may be wrong.)

  • Insert a few rows to ar

Here is what I want to achieve:

  • Read the requested aa record.

WITH OR WITHOUT USING A TRANSACTIONS, LOCK ar, SO ANY SUBSEQUENT REQUEST ATTEMPTING TO READ FROM ar WILL WAIT AT THIS POINT UNTIL THIS ONE FINISHES.

  • Scan the ar table to find out if the given aa record has anything stored already. (Assume it has not.) The problem is that in case of two simultaneous requests, both find there are no records in ar for the given aa record and they both proceed to insert the same rows twice. Otherwise, if there are, this sequence is interrupted and no INSERT occurs.

  • Consult ai in order to find out what is to be stored in ar for the given aa record. (ai seems somewhat irrelevant, but I found that it too has to be involved in the locking… may be wrong.)

  • Insert a few rows to ar

RELEASE THE LOCK ON ar

Seems simple enough, I was unsuccessful in avoiding the duplicates. I'm testing the simultaneous requests from a simple command in a Bash shell (using wget).

I have spent a while learning how exactly locking works with the InnoDB engine here http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html and here http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html and tried several ways to utilise the lock(s), still no luck.

I want the entire ar table locked (since I want to prevent INSERTs from multiple request to occur to it) causing further attempts to interact with this table to wait until the first lock is released. But there's only one mention of "entire table" being locked in the documentation (Intention Locks section in the first linked page) but that's not further discussed or I was unable to figure how to achieve it.

Could anyone point in the right direction?

marekful
  • 14,986
  • 6
  • 37
  • 59

1 Answers1

1
SET tx_isolation='READ-COMMITTED';
START TRANSACTION;
SELECT * FROM aa WHERE id = 1234 FOR UPDATE;

This ensures that only one thread gets access to a given row in aa at a time. No need to lock the ar table at all, because any other thread who may want to access row 1234 will wait.

Then query ar to find out what rows exist for the corresponding aa, and decide if you want to insert more rows to ar.

Remember that the row in aa is still locked. So be a good citizen by finishing your work quickly, and COMMIT promptly.

COMMIT;

This allows the next thread who has been waiting for the same row of aa to proceed. By using READ-COMMITTED, it will be able to see the just-committed new rows in ar.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the prompt, and seemingly pro-level reply. I'll give it a go right away and update you if it worked. – marekful Oct 16 '14 at 20:00
  • Of course (although the question is already too long) the real life situation is a lot more complex and many other queries are involved during the request. So far I'm running into `General error: 2014 Cannot execute queries while other unbuffered queries are active` but will not give up, this seems the way to go. – marekful Oct 16 '14 at 20:15
  • 1
    @MarcellFülöp, you might like to read my answer to [Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active](http://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries/17582620#17582620) – Bill Karwin Oct 16 '14 at 21:47
  • thank you for the linked answer too. It didn't take me much closer to solving the problem, especially given that, in my application, when the first 3 statements (in the first code block of your answer) are executed, then whatever is the next statement (let it be SELECT or INSERT) it will fail with this error code. Why could that be? I tried placing those 3 statements in different places of my application code - same result, next statement fails. – marekful Oct 16 '14 at 22:24
  • Without seeing your code I can't offer a solution. I suggest you search StackOverflow for other answers to the same error message, and if you don't find a solution, then post a new question. – Bill Karwin Oct 16 '14 at 22:36
  • 1
    Thanks again for this answer. I have created a simplified version of the problem where it did work perfectly. Although the project my question refers to is other developers work and it's hugely complex (using Doctrine 1.2) and I couldn't apply this solution to it, I have found a workaround. – marekful Oct 17 '14 at 12:49