How to place range/table lock at 2 tables at MS SQL server via pyodbc

Question

I am now processing 2 tables at the same time by pyodbc. Before finishing processing, I would like to place locks on the 2 tables so that nobody can change the 2 tables before I finish. How can I do that?

I tried the below, but failed with an error.

import pyodbc
conn = pyodbc.connect("conn_str")
conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
conn.execute("begin transaction trans;")
cur1 = conn.execute("select * from tbl1;")
cur2 = conn.execute("select * from tbl2;")
... some processing at cur1 and cur2 at python ...
conn.execute("commit transaction trans;")

However, the following error comes up when execution "cur2 = conn.execute("select * from tbl2;")"

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)

Is there anything wrong with my code? Highly appreciated for any help.

Have you seen pyodbc's [wiki entry](https://github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management#specifying-a-transaction-isolation-level) that talks about transaction isolation levels? — Gord Thompson, Sep 23 '18 at 15:21
You need to run the queries on different connections since only one can be active on a connection at a time (unless you were to use MARS). — Dan Guzman, Sep 23 '18 at 15:31
@GordThompson: I tried exactly the same at the link, but it failed with the following error [pyodbc.Error: ('HY011', u'[HY011] [Microsoft][ODBC SQL Server Driver]Operation invalid at this time (0) (SQLSetConnectAttr)')]. It seems that the isolation level cannot be changed after the connection is open [pyodbc.connect()]. However, it seems that pyodbc does not allow me to create an pyodbc.Connection directly without a connection str specified. — kzfid, Sep 24 '18 at 03:07
@DanGuzman I tried again. FInally, I can do it at the same connection. But I need to consume cur1 (for example, for row in cur1: do sth) before using cur2. Although I can solve the error, it is against my original thoughts that locking the 2 tables together as soon as possible. — kzfid, Sep 24 '18 at 03:10
re: "Operation invalid at this time" - Sorry about that. The instructions on the wiki page were incorrect. They have been fixed. — Gord Thompson, Sep 24 '18 at 04:00
This may be useful: https://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes . With `TABLOCK` hint you may be able to do something like this: `SELECT TOP 1 1 FROM TableA WITH (TABLOCK, HOLDLOCK) UNION ALL SELECT TOP 1 1 FROM TableB WITH (TABLOCK, HOLDLOCK)` to lock both tables at once. Then you can read them again to get data. — Alex, Sep 24 '18 at 04:02
@GordThompson: Thanks for your update. It is ok now. It seems that I cannot change the isolation level after transaction starts instead of connection started. So, you use "autocommit = False" initially to suppress the the transaction. However, as I know, in "SERIALIZABLE" mode, table is locked by range lock when it is selected. So, if I want to lock 2 tables/ranges at the same time. It seems that that I can only do a query, something like "select 1 from tbl1 where col1 = 1 union all select 1 from tbl2 where col1 = 1", which is quite dummy and dirty I think. Is there any proper way to do so? — kzfid, Sep 24 '18 at 06:24
@Alex: Thanks. It seems that (TABLOCK, HOLDLOCK) is for the whole table. Sometimes, in my case, range lock is need while sometimes, table lock is needed. Also, it needs a dummy SQL statement in order to lock. Is it a proper way to do so or any other more elegant ways? After a few researches, it seems that it is the only way to do so. Just want to find out better way if any. — kzfid, Sep 24 '18 at 06:33
As @DanGuzman hinted in an earlier comment, you could try appending `;MARS_Connection=yes` to your connection string. — Gord Thompson, Sep 24 '18 at 11:17
@GordThompson Thanks for your reminder. Just tried but it seems that my server does not support MARS, so the parameter is a kind of no function here.It seems that I either do a dummy select in order to lock 2 tables at the same time or just select them separately in a short time. By the way, I would to mark your reply as answer, but I cannot do it on a comment. Maybe you can change to post an answer? — kzfid, Sep 25 '18 at 02:28

Gord Thompson · Accepted Answer · 2018-09-25T13:16:33.187

Originally, SQL Server ODBC was limited to one active hstmt (the ODBC equivalent of a pyodbc.cursor) per connection. Later on, Microsoft added the MARS (Multiple Active Result Sets) feature to SQL Server ODBC, but that feature is "off" by default.

So this code

import pyodbc
import sys

print(f"Python version {sys.version}")  # Python version 3.6.4 ...
print(f"pyodbc version {pyodbc.version}")  # pyodbc version 4.0.24
conn_str = (
    r'DRIVER=ODBC Driver 17 for SQL Server;'
    r'SERVER=.\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str, autocommit=True)

cnxn.set_attr(pyodbc.SQL_ATTR_TXN_ISOLATION, pyodbc.SQL_TXN_SERIALIZABLE)
cnxn.autocommit = False  # enable transactions

cur1 = cnxn.execute("SELECT 1 AS x UNION ALL SELECT 2 AS x")
cur2 = cnxn.execute("SELECT 'foo' AS y UNION ALL SELECT 'bar' AS y")

print(cur1.fetchone())
print(cur2.fetchone())
print(cur1.fetchone())
print(cur2.fetchone())

fails with

Traceback (most recent call last):
  File "C:/Users/Gord/PycharmProjects/py3pyodbc_demo/main.py", line 18, in <module>
    cur2 = cnxn.execute("SELECT 'foo' AS y UNION ALL SELECT 'bar' AS y")
pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)')

However, if we add MARS_Connection=yes to the connection string

conn_str = (
    r'DRIVER=ODBC Driver 17 for SQL Server;'
    r'SERVER=.\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
    r'MARS_Connection=yes;'
)

then the code works.

Unfortunately in your case you are using the ancient DRIVER=SQL Server which is too old to support MARS_Connection=yes so your options are

use a newer version of the SQL Server ODBC driver, or
open two separate connections, one for each cursor.

How to place range/table lock at 2 tables at MS SQL server via pyodbc

1 Answers1