AB#40573

GitHub Issue: #337

Summary

This pull request addresses a critical issue in connection pooling for the mssql_python library by ensuring that the transaction isolation level is properly reset when a pooled connection is reused. Previously, the isolation level could leak between usages, potentially causing unexpected behavior. The changes include an explicit reset of the isolation level in the connection reset logic and a new test to verify this behavior.

Bug fix for connection pooling session state:

• Explicitly reset the transaction isolation level to READ COMMITTED in the Connection::reset() method to prevent isolation level settings from leaking between pooled connection usages. This addresses the limitation of SQL_ATTR_RESET_CONNECTION, which does not reset the isolation level. (mssql_python/pybind/connection/connection.cpp)

Testing improvements:

• Added a new test test_connection_pooling_isolation_level_reset in tests/test_009_pooling.py to verify that the isolation level is reset to the default when a connection is reused from the pool, preventing session state leakage.
• Imported the mssql_python module in tests/test_009_pooling.py to support new test functionality.

  315     LOG("Resetting transaction isolation level to READ COMMITTED");
  316     ret = SQLSetConnectAttr_ptr(_dbcHandle->get(), SQL_ATTR_TXN_ISOLATION,
  317                                 (SQLPOINTER)SQL_TXN_READ_COMMITTED, SQL_IS_INTEGER);
  318     if (!SQL_SUCCEEDED(ret)) {
! 319         LOG("Failed to reset transaction isolation level (ret=%d). Marking as dead.", ret);
! 320         disconnect();
! 321         return false;
! 322     }
  323     
  324     updateLastUsed();
  325     return true;
  326 }