Which MySql InnoDB Transaction Isolation Level Should I use?

33
January 13, 2019, at 02:00 AM

I am using the .NET MySqlClient v6.6.4.0 against a MySql Server v5.7.19

Show table Status 

reveals all tables are using the InnoDB engine

SHOW VARIABLES LIKE 'tx_isolation';

returns

tx_isolation    REPEATABLE-READ

My problem is simple.

I have a single process which does the following every minute

Open a database connection
Begin a Transaction (using default isolation mode of RepeatableRead)
Delete all rows from a table
Insert a bunch of rows - at least 50 or 60
Commit transaction
Close connection

I also have multiple separate processes which may query that table at any time.

I am expecting that if the table is queried while it is being written in the scope of the transaction, the reader will block until the transaction is complete and will then get back all the 50 or 60 rows.

What I find instead is that the reader does not block and receives whatever has been written up to that point.

What isolation level do I need so that readers block while the table is being populated and receive a full set of data?

Answer 1

I would not change the transaction isolation level because of this. I would rather use an explicit table write lock:

The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Lock requests for the table by other sessions block while the WRITE lock is held.

Since lock and unlock table statements commit any open transaction in the given sesion, you need to be careful how you use them. Mysql provides the following example in the above link:

SET autocommit=0;
LOCK TABLES t1 WRITE;
... do something with table t1 here ...
COMMIT;
UNLOCK TABLES;
READ ALSO
Android Pie 9.0 not playing the audio

Android Pie 9.0 not playing the audio

I am getting the following errors when I run my app on Android 90

63
Not updating ViewModel after Retrofit CREATE request

Not updating ViewModel after Retrofit CREATE request

I'm using the Retrofit client to perform HTTP requests in a simple Android projectWhen I send a CREATE request, the item is successfully created in the remote SQLite database, but the ViewModel still contains the old list

47
how to execute a service correctly with @POST in java with retrofit2

how to execute a service correctly with @POST in java with retrofit2

I'm using the retrofit2 library, the method I use is an @POST but I do not understand why it gives me an error, I currently send the data in an @Body however in postman I have it in the following way

58