Which MySql InnoDB Transaction Isolation Level Should I use?

user_69374 0
Viewed 279

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?

locking mysql c#
1 Answers

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;
user_53899970
Related