maria-discuss team mailing list archive

[Jesse Schalken <me@xxxxxxxxxxxxxxxxx>]

Hi Sergei

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.
>

I understand it's not acquiring an exclusive lock on a row because the row
doesn't exist.

However, if the SELECT didn't acquire any locks both transactions would
insert the same row and finish. So the SELECT is obviously locking
*something* to cause the INSERT to deadlock. My question is, why is that
lock (whatever it is - a gap lock?) behaving like a shared lock rather than
an exclusive lock as requested?

I did find this line in the MySQL manual (here
<https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html>):

Gap locks in InnoDB are “purely inhibitive”, which means they only stop
> other transactions from inserting to the gap. They do not prevent different
> transactions from taking gap locks on the same gap. Thus, a gap X-lock has
> the same effect as a gap S-lock.


Is this the reason for the behaviour?


> 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.
>

I'm not particularly interested in the unique key solution because my real
table can actually have multiple rows for that key. The uniqueness
constraint only applies to this particular transaction/process, not the
data itself. I don't want this process to insert multiple rows for the same
`col` value, but others may.

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';
>   SELECT RELEASE_LOCK(4);
>

This sounds like the best solution to me, if I can't otherwise acquire an
exclusive lock on a particular value in a column.

Thanks