Deadlock error in mssql DB

[Don Dwiggins]

I've been successfully using SA 0.8.0 under Windows with SQL Server 2008 server.  However, the following occurred recently when starting up the application (but only once -- restarting succeeded):

 File "Subhandler.pyo", line 138, in getTable

 File "sqlalchemy\schema.pyo", line 333, in __new__

 File "sqlalchemy\schema.pyo", line 397, in _init

 File "sqlalchemy\schema.pyo", line 425, in _autoload

 File "sqlalchemy\engine\base.pyo", line 1603, in run_callable

 File "sqlalchemy\engine\base.pyo", line 1126, in run_callable

 File "sqlalchemy\engine\default.pyo", line 258, in reflecttable

 File "sqlalchemy\engine\reflection.pyo", line 463, in reflecttable

 File "LowerCaseInspector.pyo", line 34, in get_foreign_keys

 File "sqlalchemy\engine\reflection.pyo", line 321, in get_foreign_keys

 File "<string>", line 1, in <lambda>

 File "sqlalchemy\engine\reflection.pyo", line 49, in cache

 File "sqlalchemy\dialects\mssql\base.pyo", line 1050, in wrap

 File "sqlalchemy\dialects\mssql\base.pyo", line 1059, in _switch_db

 File "sqlalchemy\dialects\mssql\base.pyo", line 1458, in get_foreign_keys

 File "sqlalchemy\engine\base.pyo", line 664, in execute

 File "sqlalchemy\engine\base.pyo", line 764, in _execute_clauseelement

 File "sqlalchemy\engine\base.pyo", line 878, in _execute_context

 File "sqlalchemy\engine\base.pyo", line 871, in _execute_context

 File "sqlalchemy\engine\default.pyo", line 320, in do_execute

DBAPIError: (Error) ('40001', '[40001] [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') u'SELECT [C].[COLUMN_NAME], [R].[TABLE_SCHEMA], [R].[TABLE_NAME], [R].[COLUMN_NAME], [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [REFERENTIAL_CONSTRAINTS_1].[MATCH_OPTION], [REFERENTIAL_CONSTRAINTS_1].[UPDATE_RULE], [REFERENTIAL_CONSTRAINTS_1].[DELETE_RULE] \nFROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C], [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R], [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS [REFERENTIAL_CONSTRAINTS_1] \nWHERE [C].[TABLE_NAME] = ? AND [C].[TABLE_SCHEMA] = ? AND [C].[CONSTRAINT_NAME] = [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME] AND [R].[CONSTRAINT_NAME] = [REFERENTIAL_CONSTRAINTS_1].[UNIQUE_CONSTRAINT_NAME] AND [C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [R].[ORDINAL_POSITION]' (u'dbo_corporatedata', u'dbo')

The statement (in Subhandler, line 138) that triggered this was:

return Table(tableName, self.meta, autoload=True)

where "tableName" is one of the existing tables in the DB, which is always accessed at startup.  Apparently, the statement is querying the information schema, while (I guess) some other process had it locked.

I suppose I can wrap some exception handling code around this, but I'm curious why a Select would get into a deadlock.

--

Don Dwiggins
Advanced Publishing Technology

[Michael Bayer]

I'd look into whether you have snapshot isolation on.  With it off, whole tables lock.

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

http://blog.sqlauthority.com/2010/05/21/sql-server-simple-example-of-snapshot-isolation-reduce%C2%A0the%C2%A0blocking%C2%A0transactions/

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.