MySQL Bugs: #27589: Driver reports wrong transaction isolation level

Bug #27589	Driver reports wrong transaction isolation level
Submitted:	2 Apr 2007 14:31	Modified:	30 Apr 2007 14:01
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.14	OS:	Windows (Windows XP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
SQLRETURN rc = SQLGetConnectAttr( m_hdbc, 
SQL_ATTR_TXN_ISOLATION,
&m_nSQLAttrTxnIsolation,
SQL_IS_POINTER, 
NULL );

driver returns transaction isolation level as SQL_TXN_READ_COMMITTED.  But if you open another connection and update some data and commit the changes, the first connection does not see the changes.

Appears that connection actual transaction isolation level is SQL_TRANSACTION_REPEATABLE_READ or SQL_TRANSACTION_SERIALIZABLE not that is not what the driver is reporting.

How to repeat:
1 establish one odbc connection
2 read some data
3 open another odbc connection
4 modified the data
5 commit the changes
6 re-read the data using connection 1, changes are not visible

Suggested fix:
Report session transaction isolation level correctly. For example, the InnoDB default is REPEATABLE READ.

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

If you've set SQL_ATTR_TXN_ISOLATION, the correct value will be returned, but we aren't properly initializing the value when it has not been set.

Get txn isolation level from the server to avoid being wrong

Attachment: bug27589.diff (application/octet-stream, text), 5.99 KiB.

The attached patch fixes the driver to query the transaction isolation level from the server when it does not already know it, instead of assuming a default. (And to compound the original problem, the previous default in the driver was out of sync with the server's default.)

The fix for this bug has been committed, and will be part of the next release (3.51.15). Thanks for the bug report.

A note has been added to the 3.51.15 changelog.