QST: to_sql is unnecessarily blocked because of dependency on information_schema tables

[match-gabeflores]

Research

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow.

Link to question on StackOverflow

https://stackoverflow.com/questions/64610269/sqlalchemy-hangs-during-insert-while-querying-information-schema-tables

Question about pandas

I'd like to bring attention to this issue where to_sql is being blocked by an unrelated process because pandas requires checking information_schema metadata tables.

My example is MSSQL (SQL Server) specific but I think it could apply to other DB systems.

steps to reproduce:

• run python code first (automatically creates table)
• run create table (new unrelated table) in an open transaction
• • key point is that this is unrelated to the table you are inserting in pandas
• re-run python code to insert 10 records. this gets blocked bc it can't query information_schema

Other scenarios that lock information_schema cause this blocking in pandas (rebuilding clustered columnstore index on large table)

Notes

In my opinion, we should be able to insert into an existing table no matter if there are other processes going on.

• for someone who knows the pandas internals, maybe they can chime in?

• see relevant Stack Overflow question

• Sqlalchemy GH issue - SQLAlchemy hangs during insert while querying [INFORMATION_SCHEMA].[TABLES] when an open transaction exists that has created a new table sqlalchemy/sqlalchemy#5679

Root cause

• This only occurs if the table name contains any uppercase letters.
• Per comments below, it is related to pandas case_sensitivity check line number.
  • Due to this PR for mysql
  • See this SQL Alchemy comment

**SQL code to create unrelated table **

begin tran 
-- in same database 
create table dbo.MyOtherTableUnrelatedProcess
(
	a int
)
--rollback

code i used for testing the python piece

import pandas as pd
import sqlalchemy as sa
import urllib

df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva', 'Frank', 'Grace', 'Henry', 'Isabel', 'Jack']
})

host = 'myhost'
schema = 'mydb'

params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};"
                                 "SERVER=" + host + ";"
                                "DATABASE=" + schema + ";"
                               "trusted_connection=yes")

engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params), fast_executemany=True)

print('writing')
# note: MyMainTable is blocked because of the open transaction on unrelated process. to_sql can't querying information_schema
df.to_sql('MyMainTable', con=engine, if_exists='append', index=False)  
print('finished')

checking the server, this is the hung query called by pandas/sqlalchemy

SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(@P1 AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(@P2 AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]

Pandas is blocked in loading into the table

[phofl]

Thanks for your report. Investigations welcome.

[bjornasm]

Interesting, this solved my problem - getting table "xyz" already exists despite having selected the option if_exists='append'. Thx for the help - at least there should be added a more helpful error message: "Is the database preoccupied? Try waiting for conflicting process is closed before trying again." or similar if the correct if_exists flag is selected?

[jacobshaw42]

Not sure if anyone else has tracked down the issue.

This is caused by check_case_sensitive in pandas.io.sql.py

This is because of the get_table_names method in sqlalchemy.dialects.mssql.base.py not using a nolock hint

[match-gabeflores]

Thanks @jacobshaw42 for tracking it down!

highlighting both lines below.

• pandas/pandas/io/sql.py

  Line 1943 in 7212ecd

  if not name.isdigit() and not name.islower():

• • due to this PR - SQL: add warning for mismatch in provided and written table name (GH7815) #8180
• • and BUG/ENH: to_sql fails to detect tables via if_exists on mysql with capitalised table names #7815

• https://github.com/sqlalchemy/sqlalchemy/blob/b468775b5c8eb7ac17638d02fdca98b73c02bb94/lib/sqlalchemy/dialects/mssql/base.py#L3348

• Note, this only gets blocked if the table name has an uppercase letter or all characters are digits.

Do you want to create an issue on SqlAlchemy repo? Otherwise, I can.

edit: i think it is something Pandas should solve, actually. it should only do the case sensitivity check for database with case sensitivity. i'm also unsure if it should be doing it at all.
or if SQLAlchemy can read from the Info_schema tables without getting blocked (maybe using READ UNCOMMITTED)?

[match-gabeflores]

I tested changing the isolation level to Read Uncommited and confirmed that to_sql was able to finish running.

It still would be good to have a better solution - still unclear if SQLAlchemy needs to fix or Pandas?

• Maybe Pandas should only do this case-sensitivity check for databases that are case-sensitive?

# https://docs.sqlalchemy.org/en/20/dialects/mssql.html#transaction-isolation-level
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params)
                          , fast_executemany=True
                           , isolation_level="READ UNCOMMITTED"
                          )

[jacobshaw42]

@match-gabeflores

Thanks for the confirmation.

I am actually use dask, which takes the uri instead of the full engine. Attempted to pass

engine_kwargs={"fast_executemany":True, "isolation_level":"READ UNCOMMITTED"}

to the dask to sql method, but it still got blocked.

I will create and issue under sqlalchemy