CODEWITHSUNDEEP
pythonmysqlpython-3.xtransactionsmysql-connector-python
John Smith Optional
John Smith Optional

Reputation: 24846

Why won't start_transaction set the transaction isolation level?

The start_transaction method of the MySQLConnection class of mysql-connector-python doesn't seem to work as expected.

>>> from mysql.connector import connect
>>> conn = connect(user = 'john', unix_socket = '/tmp/mysql.sock', database='db7')
>>> cur = conn.cursor()
>>> cur.execute("select @@session.tx_isolation")
>>> cur.fetchone()
('REPEATABLE-READ',)
>>> cur.close()
True
>>> conn.start_transaction(isolation_level = 'SERIALIZABLE')
>>> cur = conn.cursor()
>>> cur.execute("select @@session.tx_isolation")
>>> cur.fetchone()
('REPEATABLE-READ',)

The isolation level remains REPEATABLE-READ despite setting the isolation level to 'SERIALIZABLE' in the start_transaction call. Am I missing something?

Upvotes: 0

Views: 851

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179084

SELECT TRX_ISOLATION_LEVEL
  FROM information_schema.innodb_trx
 WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

This should give you your current transaction's isolation level. However, unless you START TRANSACTION WITH CONSISTENT SNAPSHOT; the transaction won't likely appear here until after InnoDB has seen you run at least one query.

Upvotes: 1

John Smith Optional
John Smith Optional

Reputation: 24846

It seems I found the answer to my own question.

This is because

select @@session.tx_isolation

returns the "session transaction isolation", which actually is not always the current transaction isolation level. If you issue a

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

before a 

START TRANSACTION

the transaction will be SERIALIZABLE, but the @@session.tx_isolation remains the same.

The "session transaction isolation" seems to be a default value for transactions when you don't specify the isolation before starting a transaction.

They say here that it's impossible to find out the current transaction isolation level:

https://bugs.mysql.com/bug.php?id=53341

Upvotes: 0

Related Questions