Search code examples
mysqltransactionslockinginnodbacid

Locking during a transaction in MySQL InnoDB

I was wondering whether all tables modified in a transaction in MySQL (InnoDB) are locked for the entire transaction?

I have a session that updates a "latest version" in a version table, then creates a new table for a few GBs of data for the new version. Another session consistently grabs the latest version and then the data for that latest version; I've found that this session sometimes returns 0 rows of data.

Here's a simplified example in SQL:

Session 1

Say we already have a table data_v41, and the latest_version in version_table is 41. This session is updates the data to the latest version.

BEGIN TRANSACTION;

-- Increment the latest version...
UPDATE version_table SET latest_version = 42;

-- Create a new table for loads of data...
CREATE TABLE data_v42 (id int NOT NULL AUTO_INCREMENT, data TEXT);

-- Insert a few GBs of data here (so this is quite slow to write to disk)...
INSERT INTO data_v42 VALUES (...);
INSERT INTO data_v42 VALUES (...);
...

COMMIT;

Session 2

This session reads the latest version from the version table, and then uses that number to choose the table from which to read the latest data:

-- Say this returns 42 and we use that number in the 2nd query...
SELECT latest_version FROM version_table;

-- Is it possible for this query to return 0 rows?
SELECT * FROM data_v42;

Session 2 seems to return 0 rows of data sometimes, which leads me to believe the latest version is set before any data has been inserted into the new table. This doesn't seem to fit with transactions being atomic...

Do I need to re-arrange my queries in Session 1 to update the version after inserting all the GBs of data?

Solution

  • In order for the reflection of your data in the session2 you need to reorder those queries

    Since the commit is after the insertion of all data it is possible for the transaction to take some amount of time which depends on the data and the amount of the data you are inserting

    So the table will be empty in your other session and the create table in session1 would have committed as it is a implicit commit operation on mysql any operations that are done before the insert will also be committed. Which makes your session2 to get the updated value in the version table

    Sol:

    Reorder the queries so that after the creation and the whole operations of insert the version table is updated. This will make your system reliable