23

How do I determine the isolation level in use for a given query? After a query is executed (by a 3rd party application) I'd like to know which isolation level was used (e.g., read uncommitted).

To be clear, I'm currently working on an application that uses EF4 running against mysql 5.1. I'm try to test different coding patterns to change isolations levels for specific EF4 queries. I need to be able to test and make sure the isolation levels are being set correctly.

reustmd
  • 3,513
  • 5
  • 30
  • 41

1 Answers1

36
SHOW VARIABLES LIKE 'tx_isolation';

or if you have MySQL 5.1+

SELECT * FROM information_schema.session_variables
WHERE variable_name = 'tx_isolation';

If you want to know what the server has configured globally, change the above to the following:

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

or if you have MySQL 5.1+

SELECT * FROM information_schema.global_variables
WHERE variable_name = 'tx_isolation';

If you want to make the query reveal what transaction isolation is being used, run this:

SELECT variable_value IsolationLevel
FROM information_schema.session_variables
WHERE variable_name = 'tx_isolation';

DISCLAIMER : I DO NOT KNOW EF4

If you are allowed to embed subqueries in the SQL about to be run by EF4, you may have to embed this query as a subquery (or embed you query as a subquery) and display the variable IsolationLevel along with the results of the actual query.

Pang
  • 9,564
  • 146
  • 81
  • 122
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • How do I map the results of that query to the actual query that had that isolation level? – reustmd Mar 18 '11 at 03:28
  • Run this query before launching your actual query within the same DB Connection – RolandoMySQLDBA Mar 18 '11 at 03:32
  • A 3rd party application is running the query I'm trying to test. So I cannot do this within the same DB connection. – reustmd Mar 18 '11 at 03:33
  • 1
    Connect to MySQL using the mysql client and run "SELECT * FROM information_schema.global_variables WHERE variable_name='tx_isolation' instead of querying the session_variables table. I'll update my answer. – RolandoMySQLDBA Mar 18 '11 at 03:36
  • With my last answer, I have a question. Do you know if the 3rd party app changes the isolation level to something different from the global setting or do all queries run on the same isolation level as dictated by the global setting ? – RolandoMySQLDBA Mar 18 '11 at 03:51
  • 'INFORMATION_SCHEMA.SESSION_VARIABLES' and 'information_schema.global_variables'feature is disabled in mysql5.6 – Richard Chan Jan 27 '18 at 14:39
  • @RichardChan MySQL 5.7 has an option called show_compatibility_56. Add that to my.cnf and restart MySQL. It will work then. – RolandoMySQLDBA Jan 27 '18 at 18:47