-1

In MYSQL if a conditional insert is performed with autocommit ON, ie.

set autocommit true;
insert into blah (x,y,z) values (1,2,3) where not exists (.....);

Would the above statement be executed atomically and committed at the same time? Or is it possible that there can be a delay between executing the insert and doing the commit?

EDIT: Updated the insert statement to reflect more accurate query:

set autocommit true;
insert into foo (x,y,z) select 1,2,3 from dual where not exists (select 1 from bar where a = 1);

I want to insert only if a row in another table does not exist. What I want to confirm is that in the below scenario there will be a failure:

SESSION1: insert into foo ..... where not exists (select 1 from bar where a = 1);

SESSION2: insert into bar (a) values (1);

SESSION2: commit;

SESSION1: commit; // should fail here.

user2905119
  • 49
  • 1
  • 4

1 Answers1

2

The way it works is the same as not using autocommit, but you begin a new transaction, immediately do your INSERT, and then immediately COMMIT without delay.

START TRANSACTION;
INSERT ...
COMMIT;

This is atomic, in the sense that no other client will see your INSERT in a partially-finished state. Atomicity isn't about speed, it's about making sure the change is either committed fully or else not at all. No half-committed state is visible to other sessions.

By the way, the syntax you show, INSERT INTO ... VALUES ... WHERE NOT EXISTS ... is not meaningful. INSERT does not have a WHERE clause. You may be thinking of an INSERT that uses rows output by a SELECT statement:

INSERT INTO ...
SELECT ... FROM ... WHERE ...;

If you do this, you would NOT use a VALUES() clause for your INSERT.

Given your updated question, it cannot work the way you show.

SESSION1: insert into foo ..... where not exists (select 1 from bar where a = 1);

If you use the default transaction isolation level of REPEATABLE-READ, this will acquire a gap lock on bar, where the row where a=1 would exist. It does this to ensure that there is no change to the latest committed entries in the table that the query was reading.

SESSION2: insert into bar (a) values (1);

This causes session to wait, because it cannot lock the gap to insert into. It will time out with an error unless Session 1 commits within innodb_lock_wait_timeout seconds (default 50).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks Bill for your response. I have updated my question based on your answer. Please respond accordingly. – user2905119 Jan 24 '20 at 00:16
  • Based on https://stackoverflow.com/questions/17068686/how-do-i-lock-on-an-innodb-row-that-doesnt-exist-yet we need to use FOR UPDATE keyword in the select clause to create a lock on a row that is not existing. Also there is a possibility that it might perform a full table lock. Is that true? – user2905119 Jan 25 '20 at 05:45
  • It depends on if your query condition is on an indexed column or not. If yes, it'll create a gap lock. If the column isn't indexed, it'll effectively lock the whole table (including gaps). – Bill Karwin Jan 25 '20 at 06:50