pymssql默认关闭自动模式开启事务行为浅析

使用Python采集SQL Server数据库服务器磁盘信息时，遇到了一个错误“CONFIG statement cannot be used inside a user transaction.DB-Lib error message 20018, severity 16”，那么为什么遇到这个错误呢？ 其实很简单，就是因为SQL Server事务中不允许使用RECONFIGURE，我们可以简单模拟构造一下这个错误，如下所示：

 

BEGIN TRAN
     EXEC sp_configure 'show advanced options', 1
     RECONFIGURE WITH OVERRIDE;
COMMIT TRAN;

 

 

 

我的Python脚本中，访问数据库的SQL没有使用事务（没有BEGIN TRAN ... COMMIT TRAN），那么是否pymssql中默认会开启事务呢？ 我们可以构造一个Python脚本访问SQL Server 数据库，然后我们使用SQL Profile跟踪一下，就基本上能知道是否pymssql会默认开启事务。Python脚本TranTest.py如下所示：

 

# -*- coding: utf-8 -*-
'''
-------------------------------------------------------------------------------------------
--  Script Name     :   TranTest.py
-------------------------------------------------------------------------------------------
'''
import pymssql
import logging
import os.path
import os
import base64
from cryptography.fernet import Fernet





key=bytes(os.environ.get('key'),encoding="utf8")
cipher_suite = Fernet(key)
with open('/home/konglb/python/conf/ms_db_conf.bin', 'rb') as file_object:
    for line in file_object:
        encryptedpwd = line
decrypt_pwd = (cipher_suite.decrypt(encryptedpwd))
password_decrypted = bytes(decrypt_pwd).decode("utf-8") #convert to string
env_db_user=os.environ.get('db_user')
db_user=base64.b64decode(bytes(env_db_user, encoding="utf8"))


dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),
                               user=bytes.decode(db_user),
                               password=password_decrypted,
                               database='master',
                               charset="utf8");

sub_cursor = dest_db_conn.cursor(as_dict=True)


sub_cursor.execute('SELECT COUNT(*) AS RecordNum FROM msdb.dbo.sysmail_account')
result_rows =sub_cursor.fetchone()

print(result_rows["RecordNum"])

#dest_db_conn.commit()
dest_db_conn.close()

 

如下截图所示，我们发现pymssql会对任何访问SQL Server的SQL加上BEGIN TRAN，也许眼尖的同学发现了端倪，SQL Profile捕获的SQL，有BEGIN TRAN，但是没有COMMIT TRAN，这个是因为上面的Python代码中没有提交事务（#dest_db_conn.commit() 注释了）

 

 

 

 

修改上面Python代码，在关闭数据库连接前，加上一行代码dest_db_conn.commit()，然后重复上面实验就能看到COMMIT TRAN了，如下所示：

 

dest_db_conn.commit()
   dest_db_conn.close()

 

 

 

那么pymssql中是否可以关闭事务呢？ 因为有些普通、简单的查询，根本没有必要使用事务，其实pymmsql的Connection接口其实是提供了这么一个功能的，官方文档的介绍如下：

 

Connection object methods

 

Connection.autocommit(status)

Where status is a boolean value. This method turns autocommit mode on or off.

By default, autocommit mode is off, what means every transaction must be explicitly committed if changed data is to be persisted in the database.

You can turn autocommit mode on, what means every single operation commits itself as soon as it succeeds.

A pymssql extension to the DB-API 2.0.

 

 

我们知道，SQL Server在默认情况下数据库连接处于自动提交模式(autocommit mode)，每个SQL命令一旦被执行便提交给数据库，一旦提交就无法回滚。 在数据库中不支持事务的情况下，自动提交模式是唯一支持的模式。 在此类数据库语句仅在提交后可以执行它们并没有方法回滚它们;它们因此始终处于自动提交模式.

 

那么我们测试一下就会发现autocommit=True的情况下，pymmsql不会自动给SQL加上BEGIN TRAN了（测试期间，犯了个迷糊，弄混了一个前提条件，而且没有充分测试，就做出了一个相反的结论，后续自己一直折腾时才发现这个问题）

 

修改前代码：

 

dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),
                               user=bytes.decode(db_user),
                               password=password_decrypted,
                               database='master',
                               charset="utf8");

 

修改后代码：

 

 

dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),
                               user=bytes.decode(db_user),
                               password=password_decrypted,
                               database='master',
                               charset="utf8",

                               autocommit=True);

 

 

关于pymssql默认情况下会关闭自动提交模式（autocommit mode）开启事务的行为，一定要小心，如果你SQL脚本里面有DML操作而且忘记加commit时，那么可能造成很多不必要的阻塞。而且相信很多不明所以的同学还会一脸懵逼。

 

 

 

参考资料：

 

    https://github.com/pymssql/pymssql/issues/460

    http://pymssql.org/en/stable/ref/pymssql.html#connection-object-methods