Bug #31959 Allows dirty reading with SQL_TXN_READ_COMMITTED isolation through ODBC
Submitted: 30 Oct 2007 16:23 Modified: 26 Feb 2008 12:19
Reporter: John Water Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.20 OS:Microsoft Windows
Assigned to: Jess Balint
Tags: dirty reading, Isolation level, ODBC driver
Triage: D2 (Serious)

[30 Oct 2007 16:23] John Water
Description:
It seems there is a bug in the MySQL ODBC driver 03.51.20 with the MySQL Server 5.1.22. This MySQL ODBC driver or the server allows dirty reading even after the isolation level is set to SQL_TXN_READ_COMMITTED with the following ODBC API 
SQLINTEGER isolation = SQL_TXN_READ_COMMITTED; 
ret = SQLSetConnectAttr( conn->dbc, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER)(a_ptrint)isolation, SQL_IS_UINTEGER ); 

I have two apps: app 1 issues inserts to a table named test (without commit) and then goes to sleep and app 2 fetches rows from the test table when the first app is sleeping. I can see app 2 will fetch all the rows inserted by app 1, even app 1 did not commit the transaction. 

This problem still happens even my table is created with the clause, "engine=InnoDB". 

Please note: the driver and server work well if my ODBC app uses the default isolation level, I believe that is SQL_TXN_REPEATABLE_READ with the MySQL ODBC driver 3.51.20

Here is my source code for app 1: 

void ShowBug( p_ml_host_conn conn ) 
/*********************************/ 
{ 
SQLHSTMT stmt; 
RETCODE ret; 
char * drop = "drop table test"; 
char * create = "create table test( c1 int primary key, c2 int ) engine InnoDB"; 
char * insert = "insert into test values( 1, 1 )"; 
SQLINTEGER isolation = SQL_TXN_READ_COMMITTED; 
ret = SQLSetConnectAttr( conn->dbc, 
SQL_ATTR_AUTOCOMMIT, 
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 
SQL_IS_UINTEGER ); 
_c( conn->host->env, conn->dbc, NULL ); 
ret = SQLSetConnectAttr( conn->dbc, 
SQL_ATTR_TXN_ISOLATION, 
(SQLPOINTER)(a_ptrint)isolation, 
SQL_IS_UINTEGER ); 
_c( conn->host->env, conn->dbc, NULL ); 
ret = SQLAllocHandle( SQL_HANDLE_STMT, conn->dbc, &stmt ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLExecDirect( stmt, (SQLCHAR *)drop, SQL_NTS ); 
ret = SQLExecDirect( stmt, (SQLCHAR *)create, SQL_NTS ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLExecDirect( stmt, (SQLCHAR *)insert, SQL_NTS ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt ); 
_c( conn->host->env, conn->dbc, stmt ); 
printf( "I am sleeping ...\n" ); 
Sleep( 20000 ); 
printf( "I am rolling back the tran\n" ); 
ret = SQLEndTran( SQL_HANDLE_DBC, conn->dbc, SQL_ROLLBACK ); 
done: 
return; 
} 

and the source code for app 2: 

void ShowBug( p_ml_host_conn conn ) 
/*********************************/ 
{ 
SQLHSTMT stmt; 
RETCODE ret; 
char * select = "select c1, c2 from test"; 
SQLINTEGER c1; 
SQLINTEGER c2; 
SQLINTEGER c1_ind; 
SQLINTEGER c2_ind; 
SQLINTEGER isolation = SQL_TXN_READ_COMMITTED; 
ret = SQLSetConnectAttr( conn->dbc, 
SQL_ATTR_AUTOCOMMIT, 
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 
SQL_IS_UINTEGER ); 
_c( conn->host->env, conn->dbc, NULL ); 
ret = SQLSetConnectAttr( conn->dbc, 
SQL_ATTR_TXN_ISOLATION, 
(SQLPOINTER)(a_ptrint)isolation, 
SQL_IS_UINTEGER ); 
_c( conn->host->env, conn->dbc, NULL ); 
ret = SQLAllocHandle( SQL_HANDLE_STMT, conn->dbc, &stmt ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLPrepare( stmt, (SQLCHAR *)select, SQL_NTS ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLBindCol( stmt, 1, SQL_C_SLONG, &c1, sizeof( c1 ), &c1_ind ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLBindCol( stmt, 2, SQL_C_SLONG, &c2, sizeof( c2 ), &c2_ind ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLExecute( stmt ); 
_c( conn->host->env, conn->dbc, stmt ); 
while( ( ret = SQLFetch( stmt ) ) != SQL_NO_DATA ) { 
printf( "ret = %d c1 = %d, c1_ind = %d, c2 = %d, c2_ind = %d\n", 
ret, c1, c1_ind, c2, c2_ind ); 
} 
ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt ); 
_c( conn->host->env, conn->dbc, stmt ); 
ret = SQLEndTran( SQL_HANDLE_DBC, conn->dbc, SQL_ROLLBACK ); 
done: 
return; 
} 

How to repeat:
Build two applications with the attached source code: compile one with -DINSERT and the second one without this flag.  Then run app 1 and app2 consecutively and then you'll see app 2 will show the un-committed insert.  The command line to start app1 or app2 could be

    odbcbug.exe "dsn=mydsn;uid=myuid;pwd=mypassword"

Here is the source code:

----------------------------------------------------------------------

// ODBCBugs.C - Simple ODBC skeleton for duplicating ODBC bugs
//              that can't be duplicated using ODBC Test.
//		The function called ShowBug() duplicates the problem.

// To run the test, the first parameter must be an ODBC connection string
// in quotes:
//
//	odbcbug "dsn=mydsn;uid=myuid;pwd=mypwd"
//

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <windows.h>
#include <process.h>
#include "odbc.h"

#if ODBCVER < 0x0350
    #error "***** This module requires ODBC 3.5 or higher *****"
#endif

typedef unsigned int		uint32;
typedef uint32 _W64		a_ptrint;

typedef struct an_odbc_info {
    SQLHENV		env;
} an_odbc_info, *p_odbc_info;

typedef struct an_odbc_conn {
    p_odbc_info	info;
    SQLHDBC		dbc;
    int			kill;
    int			done;
} an_odbc_conn, *p_odbc_conn;

#define _c( e, d, s )						\
	if( ret != 0  && ret != SQL_SUCCESS_WITH_INFO )	{	\
	    printf( "error at line: %d\n", __LINE__ );		\
	    ODBC_error( e, d, s );				\
	    goto done;						\
	}

void ODBC_error( HENV henv, HDBC hdbc, HSTMT hstmt )
/**************************************************/
{
    UCHAR	sqlstate[10];
    UCHAR	errmsg[SQL_MAX_MESSAGE_LENGTH];
    SDWORD	nativeerr;
    SWORD	actualmsglen;
    RETCODE	rc;
loop:  	rc = SQLError( (SQLHENV)henv, (SQLHDBC)hdbc, (SQLHSTMT)hstmt,
		sqlstate, &nativeerr, errmsg,
		SQL_MAX_MESSAGE_LENGTH - 1, &actualmsglen );
    if( rc == SQL_ERROR) {
	printf( "SQLError failed!\n");
	return;
    }
    if( rc != SQL_NO_DATA_FOUND ) {
	printf( "SQLSTATE = %s\n", sqlstate );
	printf( "NATIVE ERROR = %d\n", nativeerr );
	errmsg[actualmsglen] = '\0';
	printf( "MSG = %s\n\n", errmsg );
	goto loop;
    }
}

p_odbc_info Init( void )
/**********************/
{
    p_odbc_info	info;
    RETCODE	ret;

    info = (p_odbc_info) malloc( sizeof( *info ) );
    if( info != NULL ) {
	ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &info->env );
	_c( info->env, NULL, NULL );
    
	ret = SQLSetEnvAttr( info->env,
		       SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0 );
	_c( info->env, NULL, NULL );
    }

done:
    return( info );
}

void Fini( p_odbc_info info )
/***************************/
{
    RETCODE	ret;

    // Clean up any resources and shut the module down.
    if( info->env != SQL_NULL_HENV ) {
	ret = SQLFreeHandle( SQL_HANDLE_ENV, info->env );
    }
}

p_odbc_conn Connect(
/******************/
    p_odbc_info	info,
    char *	conn_str )
{
    // Establish a connection with the host database. This may
    // create a new connection or just grab one from a pool
    // of connections.
    p_odbc_conn	conn;
    SQLSMALLINT		conn_str_len;
    char		out_str[ 2048 ];
    RETCODE		ret = SQL_ERROR;

    conn = (p_odbc_conn) malloc( sizeof( an_odbc_conn ) );
    if( conn == NULL ) {
	goto done;
    }
    conn->info 	   = info;
    conn->dbc  	   = SQL_NULL_HDBC;
    conn->done	   = FALSE;

    ret = SQLAllocHandle( SQL_HANDLE_DBC, info->env, &conn->dbc );
    _c( info->env, conn->dbc, NULL );

    ret = SQLDriverConnect( conn->dbc,
			    NULL, // HWND_NULL,
			    (SQLCHAR *)conn_str,
			    SQL_NTS,
			    (SQLCHAR *)out_str,
			    sizeof( out_str ),
			    &conn_str_len,
			    SQL_DRIVER_NOPROMPT );
    _c( info->env, conn->dbc, NULL );
    if( ( ret != SQL_SUCCESS ) && ( ret != SQL_SUCCESS_WITH_INFO ) ) {
	conn = NULL;
	goto done;
    }
    // Use transactions instead of auto-commit
    ret = SQLSetConnectAttr( conn->dbc,
			     SQL_ATTR_AUTOCOMMIT,
			     (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
			     SQL_IS_UINTEGER );
    _c( info->env, conn->dbc, NULL );

done:
    return( conn );
}

void Disconnect( p_odbc_conn conn )
/*********************************/
{
    if( conn != NULL ) {
	if( conn->dbc != SQL_NULL_HDBC ) {
	    SQLDisconnect( conn->dbc );
	    SQLFreeHandle( SQL_HANDLE_DBC, conn->dbc );
	}
    
	free( conn );
    }
}

#ifdef INSERT

void ShowBug( p_odbc_conn conn )
/******************************/
{
    SQLHSTMT	stmt;
    RETCODE	ret;
    char *	drop	= "drop table test";
    char *	create	= "create table test( c1 int primary key, c2 int ) engine=InnoDB";
    char *	insert	= "insert into test values( 1, 1 )";
    SQLINTEGER	isolation = SQL_TXN_READ_COMMITTED;
    
    ret = SQLSetConnectAttr( conn->dbc,
			     SQL_ATTR_AUTOCOMMIT,
			     (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
			     SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, NULL );

    ret = SQLSetConnectAttr( conn->dbc,
			      SQL_ATTR_TXN_ISOLATION,
			      (SQLPOINTER)(a_ptrint)isolation,
			      SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, NULL );

    ret = SQLAllocHandle( SQL_HANDLE_STMT, conn->dbc, &stmt );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLExecDirect( stmt, (SQLCHAR *)drop, SQL_NTS );

    ret = SQLExecDirect( stmt, (SQLCHAR *)create, SQL_NTS );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLExecDirect( stmt, (SQLCHAR *)insert, SQL_NTS );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    _c( conn->info->env, conn->dbc, stmt );

    printf( "table is created with %s\n", create );
    printf( "I am sleeping ...\n" );
    Sleep( 20000 );
    printf( "I am rolling back the tran\n" );

    ret = SQLEndTran( SQL_HANDLE_DBC, conn->dbc, SQL_ROLLBACK );

  done:
    return;
}
#else
void ShowBug( p_odbc_conn conn )
/******************************/
{
    SQLHSTMT	stmt;
    RETCODE	ret;
    char *	select	= "select c1, c2 from test";
    SQLINTEGER	c1;
    SQLINTEGER	c2;
    SQLINTEGER	c1_ind;
    SQLINTEGER	c2_ind;
    SQLINTEGER	isolation = SQL_TXN_READ_COMMITTED;
    
    ret = SQLSetConnectAttr( conn->dbc,
			     SQL_ATTR_AUTOCOMMIT,
			     (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
			     SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, NULL );

    ret = SQLSetConnectAttr( conn->dbc,
			      SQL_ATTR_TXN_ISOLATION,
			      (SQLPOINTER)(a_ptrint)isolation,
			      SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, NULL );

    ret = SQLAllocHandle( SQL_HANDLE_STMT, conn->dbc, &stmt );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLPrepare( stmt, (SQLCHAR *)select, SQL_NTS );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLBindCol( stmt, 1, SQL_C_SLONG, &c1, sizeof( c1 ), &c1_ind );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLBindCol( stmt, 2, SQL_C_SLONG, &c2, sizeof( c2 ), &c2_ind );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLExecute( stmt );
    _c( conn->info->env, conn->dbc, stmt );

    while( ( ret = SQLFetch( stmt ) ) != SQL_NO_DATA ) {
	printf( "ret = %d c1 = %d, c1_ind = %d, c2 = %d, c2_ind = %d\n",
		ret, c1, c1_ind, c2, c2_ind );
    }
    ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLEndTran( SQL_HANDLE_DBC, conn->dbc, SQL_ROLLBACK );

  done:
    return;
}
#endif

int main( int argc, char *argv[] )
/********************************/
{
    p_odbc_info  info = NULL;
    p_odbc_conn  conn = NULL;

    info = Init();
    if( info != NULL ) {
	argc = argc; // Unused
	conn = Connect( info, argv[ 1 ] );
	if( conn != NULL ) {
	    // ===========================
	    ShowBug( conn );
	    // ===========================
	    Disconnect( conn );
	}
	Fini( info );
    }

    return( 0 );
}

------------------------------------------------------------------
[30 Oct 2007 17:18] Susanne Ebrecht
Hello John,

Thank you for reporting a bug.

Regards,

Susanne
[22 Feb 2008 20:44] Jess Balint
fix + test

Attachment: bug31959.diff (application/octet-stream, text), 3.84 KiB.

[23 Feb 2008 17:46] Jess Balint
Committed as rev 1044, will be released in 3.51.24.
[26 Feb 2008 12:19] MC Brown
A note has been added to the 3.51.24 changelog: 

When using the ODBC SQL_TXN_READ_COMMITTED option, 'dirty' records would be read from tables as if the option had not been applied.