i am using:
pymssql 0.8.0
freetds 0.63
slackware 10.2
MSSQL 2000 on windows 2000 server
everything working fine, except one thing.
when i run my application on production site
i have problem with locking in update query's
this is error produced by pymssql module:
PythonHandler index: File "/usr/lib/python2.4/site-packages/pymssql.py", line 126, in execute
self.executemany(operation, (params,))
PythonHandler index: File "/usr/lib/python2.4/site-packages/pymssql.py", line 152, in executemany
raise DatabaseError, "internal error: %s" % self.__source.errmsg()
PythonHandler index: DatabaseError: internal error: SQL Server message 1205, severity 13, state 61, procedure PR_update_tp_user_data_from_all, line 24:
Transaction (Process ID 152) was deadlocked on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
DB-Lib error message 20018, severity 5:General SQL Server error: Check messages from the SQL Server.
is there any workaround about this?
thanks for any answer
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This isn't really a problem with pymssql - it's just an issue with the database server.
You need to avoid deadlocks. Read about "deadlocks, avoiding" in SQL Server Books Online.
Of course you may still need to handle these exceptions in your python code if you can't avoid the deadlocks, or just in case. I'm not sure exactly what the python code for this would be.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
i want ask for your opinion for this deadlocking workaround:
is it possible to made it like this:
somewhere in class i call:
cur = self._dbconn.cursor() # new cursor
try:
cur.execute(sql) # if execute is OK
self._dbconn.commit() # commit
except:
self._exec_loop(sql) # if execute is deadlock
def _exec_loop(self, sql):
locked = True
attemp = 2
self._dbconn.close()
while locked: # in loop try to make new connection a try execute query
time.sleep(0.01) # with 10ms sleep time
try:
self._dbconn = pymssql.connect(...)
cur = self._dbconn.cursor()
# self.req.write("%d. attemp cur.execute<br />" % attemp)
cur.execute(sql)
locked = False
self._dbconn.commit()
except:
locked = True
attemp += 1
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I haven't analysed this fully, but one problem I see with this is the except clauses. You are handling all exceptions the same way and there could be a multitude of different problems (DB timeout, DB authorisation, SQL syntax error, etc., etc.) For a lot of these exceptions you wouldn't just want to keep trying your SQL statement.
Perhaps Andrzej Kukula can suggest what the except clause should be... That area does not seem to be well documented.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was thinking about adding error details, ie. severity, os error number, os error string, db error number, db error string, to the exception. Then you'd be able to catch the specific error, ie. if severity=13 and db_error=1205 then redo_transaction(); else raise_error_to_user().
I'd like to ask you for comments and ideas.
Thanks,
Andrzej
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Andrzej wrote:
> I was thinking about adding error details, ie. severity, os error number, os error string, db error number, db error string, to the exception. Then you'd
> be able to catch the specific error, ie. if severity=13 and db_error=1205 then redo_transaction(); else raise_error_to_user().
This would be most welcome! I haven't tried to handle any exceptions (other than log them) in my use of pymssql as I didn't know if or how I could handle particular database errors (plus it was low priority for me during the initial development). I'm afraid I'm far from an expert in exceptions and don't have any bright ideas at this stage, except to say that I hope it will be possible to also use classes of error, e.g. DbAuthorisation, ConnectFailed, DbSQLExpr, DbTimeout, DbLocked, DbFailed, SQLFailed. These names are taken from the exception handling for SQL database queries in another programming environment, BTW.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
> I haven't analysed this fully, but one problem I see with this is the except clauses.
yes, i know about it. but in my application is that exception most probably...
(sorry for my english)
when you forgot for this clause, is that code good?
maybe it will better to do this with total different code...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
hallo
i am using:
pymssql 0.8.0
freetds 0.63
slackware 10.2
MSSQL 2000 on windows 2000 server
everything working fine, except one thing.
when i run my application on production site
i have problem with locking in update query's
this is error produced by pymssql module:
PythonHandler index: File "/usr/lib/python2.4/site-packages/pymssql.py", line 126, in execute
self.executemany(operation, (params,))
PythonHandler index: File "/usr/lib/python2.4/site-packages/pymssql.py", line 152, in executemany
raise DatabaseError, "internal error: %s" % self.__source.errmsg()
PythonHandler index: DatabaseError: internal error: SQL Server message 1205, severity 13, state 61, procedure PR_update_tp_user_data_from_all, line 24:
Transaction (Process ID 152) was deadlocked on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
DB-Lib error message 20018, severity 5:General SQL Server error: Check messages from the SQL Server.
is there any workaround about this?
thanks for any answer
This isn't really a problem with pymssql - it's just an issue with the database server.
You need to avoid deadlocks. Read about "deadlocks, avoiding" in SQL Server Books Online.
Of course you may still need to handle these exceptions in your python code if you can't avoid the deadlocks, or just in case. I'm not sure exactly what the python code for this would be.
ok, this is good message.
so, i have to avoid deadlocks in MSSQL.
thanks for answer.
i want ask for your opinion for this deadlocking workaround:
is it possible to made it like this:
somewhere in class i call:
cur = self._dbconn.cursor() # new cursor
try:
cur.execute(sql) # if execute is OK
self._dbconn.commit() # commit
except:
self._exec_loop(sql) # if execute is deadlock
def _exec_loop(self, sql):
locked = True
attemp = 2
self._dbconn.close()
while locked: # in loop try to make new connection a try execute query
time.sleep(0.01) # with 10ms sleep time
try:
self._dbconn = pymssql.connect(...)
cur = self._dbconn.cursor()
# self.req.write("%d. attemp cur.execute<br />" % attemp)
cur.execute(sql)
locked = False
self._dbconn.commit()
except:
locked = True
attemp += 1
I haven't analysed this fully, but one problem I see with this is the except clauses. You are handling all exceptions the same way and there could be a multitude of different problems (DB timeout, DB authorisation, SQL syntax error, etc., etc.) For a lot of these exceptions you wouldn't just want to keep trying your SQL statement.
Perhaps Andrzej Kukula can suggest what the except clause should be... That area does not seem to be well documented.
I was thinking about adding error details, ie. severity, os error number, os error string, db error number, db error string, to the exception. Then you'd be able to catch the specific error, ie. if severity=13 and db_error=1205 then redo_transaction(); else raise_error_to_user().
I'd like to ask you for comments and ideas.
Thanks,
Andrzej
Andrzej wrote:
> I was thinking about adding error details, ie. severity, os error number, os error string, db error number, db error string, to the exception. Then you'd
> be able to catch the specific error, ie. if severity=13 and db_error=1205 then redo_transaction(); else raise_error_to_user().
This would be most welcome! I haven't tried to handle any exceptions (other than log them) in my use of pymssql as I didn't know if or how I could handle particular database errors (plus it was low priority for me during the initial development). I'm afraid I'm far from an expert in exceptions and don't have any bright ideas at this stage, except to say that I hope it will be possible to also use classes of error, e.g. DbAuthorisation, ConnectFailed, DbSQLExpr, DbTimeout, DbLocked, DbFailed, SQLFailed. These names are taken from the exception handling for SQL database queries in another programming environment, BTW.
yes yes, this would be most welcome.
i thing, that if you do this by yourself (Andrzej) people, who use this library, will assimilate with your code.
i thing that classes of error, as wrote Sebastian, are the best way.
BTW, Andrzej thank for your code, is very usefull
> I haven't analysed this fully, but one problem I see with this is the except clauses.
yes, i know about it. but in my application is that exception most probably...
(sorry for my english)
when you forgot for this clause, is that code good?
maybe it will better to do this with total different code...