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/ On Jul 8, 2013, at 3:45 PM, Don Dwiggins <[email protected]> wrote: > 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 > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
