MySQL Forums
Forum List  »  General

Please Help With Concurrency Issue
Posted by: Sam Weiss
Date: April 02, 2006 07:40PM

I have a classic concurrency problem I am trying to solve in MySQL 4.1/InnoDB.

I need to atomically read (SELECT) from a table and conditionally INSERT into that table, depending upon the result of the SELECT.

In other words, these two operations must be protected by a mutex, so that no other process/client can read from table foo until both operations have completed.

Some pseudocode to illustrate what I am trying to achieve:

mutex.start
SELECT FROM foo foo_field;

if (some condition on foo_field) {
INSERT INTO foo ...;
}
mutex.end

Now, I have a good basic understanding of transactions (though I'm rather hazy on the distinctions among the various isolation levels). And I do not think that transactions will get me what I need. For example:

START TRANSACTION
SELECT FROM foo foo_field;

if (some condition on foo_field) {
INSERT INTO foo ...;
}
COMMIT

The problem with the above, as I understand it, is that although the transaction guarantees that either the two operations both complete or neither completes, there is no guarantee that another process will not select from table foo in between the two operations of the transaction. In my case, it is critical that no other process/client select from foo in between the SELECT and INSERT statements in the above transaction.

So, it appears that what I need is a table lock:

LOCK TABLES foo WRITE
SELECT FROM foo foo_field;

if (some condition on foo_field) {
INSERT INTO foo ...;
}
UNLOCK TABLES

According to the MySQL docs, LOCK TABLES (for WRITE) will prevent any other access to the table until the lock is released.

However, I have found that other processes can indeed still read from the locked table during the time it is locked! The only way I have been able to prevent this is to surround every other bit of sql that accesses table foo with its own LOCK TABLES. For example,

LOCK TABLES foo READ
SELECT FROM foo foo_field
UNLOCK TABLES

Here are my questions:

1) Is the behavior I am seeing from LOCK TABLES the expected behavior? Shouldn't LOCK TABLES prevent other readers from reading the table until the lock is released (without having to surround every reader with its own LOCK TABLES)?

2) Am I missing something basic regarding transactions? Is there perhaps a better way to accomplish this using transactions rather than LOCK TABLES?

3) This has got to be a common problem with an established design pattern for a solution. What is it?

Thanks,

Sam

Options: ReplyQuote


Subject
Written By
Posted
Please Help With Concurrency Issue
April 02, 2006 07:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.