Deadlocks when reflecting SQL Server metadata

[Sean Harrington]

We're getting the following deadlock error on SQL Server:

[FreeTDS][SQL Server]Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') [SQL: 'SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] \nFROM [INFORMATION_SCHEMA].[TABLES] \nWHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = ? ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]'] [parameters: (u'dbo', 'BASE TABLE')]

when we run:

metadata.reflect(engine)

There is an extremely small load on this server, and I am wondering why this query would cause a deadlock. Planning on looking into this further with the SQL Server Deadlock tool: https://technet.microsoft.com/en-us/library/ms188246(v=sql.105).aspx

Anyone seen this before?

[Mike Bayer]

so do you have another thread or process that is running database
migrations, like create table or alter table, while that happens?

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Sean Harrington]

Negative - no other process/thread is executing any DDL statements.

I have a hunch that selecting from INFORMATION_SCHEMA reads all dB tables, so if any of them are read-locked, we’d get this error. Question is: in what world (or config) do table’s get read locked when no DDL is being performed? Or is there another way the INFORMATION_SCHEMA could get locked? (Ive done quite a bit with MySQL, but tread very lightly, and rarely, on Microsoft/SQL Server land.)

There also might a “too-short deadlock timeout”? Im waiting on an effing VMWare license to boot windows on my Mac and load up SQL Server Management Studio...

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/tAAb5KtKsb8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.