0

As in does it take the table's current state and insert rows in it, or just straight up inserts the row into the table not caring about the state of the table?

I'm asking this because I'm not sure as to what happens when concurrent insert rows happen at the same time.

The scenario that is playing in my head is that each insert row takes the initial state of the table and inserts a row into the table, and because they happen at the same time, race condition occurs and the row that gets inserted last overwrites the insertion of the other row, and I don't want that to happen.

Is this a correct understanding of what happens?

For more context I'm using MySQL, and picture that the rows do not conflict with each other due to unique constraints or whatsoever, they are successful inserts.

For example: I have this empty table

CREATE TABLE IF NOT EXISTS test_table (
                    testOne INT,
                    testTwo VARCHAR(255),
                    PRIMARY KEY (testOne)
                );

and these 2 insert statements happen at the same time,

INSERT IGNORE INTO test_table (testOne, testTwo)
            VALUES (1, "two");

INSERT IGNORE INTO test_table (testOne, testTwo)
            VALUES (2, "three");

will the table only have 1 row inserted? Since the initial state which each insert statement got the table was an empty one, and depending on which insert statement finishes last will overwrite the other insert statement, resulting in only 1 row inserted

I've tried opening up 4 terminals and just started inserting rows of data which don't conflict with each other into the same table at the same time. After that I got the number of rows inserted which is 9688, the data for each of the 4 terminals have the same number of rows of : 2422 so that means the race condition did not occur and all insertions were successful in this test. I tested it out 3 times and got the same result so I'm assuming the scenario that is playing in my head should be false, but you can never be too sure so I am seeking experienced advice on this because I'm not too familiar with SQL

Kevin Chin
  • 13
  • 1

1 Answers1

-1

The two INSERT statements will each execute inside a separate transaction. This means that the inserts will execute independently of each other. MySQL will, as a general rule, guarantee that each transaction will execute successfully. The only way that your second statement could overwrite the results from the first INSERT would be if that second statement were an update, or maybe delete, rather than another INSERT.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Oh so does this mean that the table will have 2 rows of data? – Kevin Chin Sep 01 '23 at 04:44
  • Yup...2 inserts should mean 2 new rows. – Tim Biegeleisen Sep 01 '23 at 04:55
  • @KevinChin the gist of this answer is correct (myswl does handle concurrent inserts), but the details of the answer are not correct as mysql has multiple table types, not all table types support transactions, transactions ensure consistency nit concurrency and handling of concurrent inserts is different for each table types. The answers to the duplicate question provide more accurate details to your question and cover more table types. – Shadow Sep 01 '23 at 06:47