maria-discuss team mailing list archive
Mailing list archive
Re: Exclusive lock not working
On Mar 17, Jesse Schalken wrote:
> I have a transaction that checks if a row exists with a particular value
> and if not, generates the data for that row and inserts it:
> -- schema
> CREATE TABLE test (
> col INT,
> data TEXT,
> KEY (col)
> -- transaction
> START TRANSACTION;
> SELECT * FROM test WHERE col = 4 FOR UPDATE;
> -- If no results, generate data and insert
> INSERT INTO test SET col = 4, data = 'generated data goes here';
> The problem I have is that if two copies of this transaction run
> concurrently (with the queries interleaved), they both pass the SELECT and
> deadlock on the INSERT. I would expect that the SELECT would acquire an
> exclusive lock on "col = 4" so only one transaction will proceed to insert,
> but the "FOR UPDATE" seems to behave as a shared lock instead.
The statement SELECT ... FOR UPDATE works for you exactly as it says. It
*selects* and locks it *for update*. Because the row doesn't exist,
SELECT actually selects nothing. So it locks nothing for update.
When the row exists, the first transaction returns a row and the second
waits on SELECT.
> What is the correct way to write this transaction so only one copy proceeds
> to generate the "data" string and insert the row?
Your goal is to "check if a row exists with a particular value
and if not, generate the data for that row and insert it". Normally
uniqueness checks are done inside a database. Make your KEY (col) a
UNIQUE key, then when you insert the database will check whether a row
exists and will insert the value if not.
If you insist on doing it in the application, you can try user-level
locks, GET_LOCK/RELEASE_LOCK functions. Like
SELECT GET_LOCK(4, 10);
-- If the result is 1
SELECT * FROM test WHERE col = 4;
-- If no results, generate data and insert
INSERT INTO test SET col = 4, data = 'generated data goes here';
Chief Architect MariaDB