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:Microsoft Windows (Windows XP)
Assigned to: Jim Winstead CPU Architecture:Any

[2 Apr 2007 14:31] Farid Zidan
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
[2 Apr 2007 18:18] Jim Winstead
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.
[3 Apr 2007 0:03] Jim Winstead
Get txn isolation level from the server to avoid being wrong

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

[3 Apr 2007 0:05] Jim Winstead
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.)
[12 Apr 2007 16:18] Jim Winstead
The fix for this bug has been committed, and will be part of the next release (3.51.15). Thanks for the bug report.
[30 Apr 2007 14:01] MC Brown
A note has been added to the 3.51.15 changelog.