Avoid Deadlocks Using Teradata and SQLAlchemy

Question

I am using sqlalchemy and sqlalchemy-teradata to query on my database. Every select statement generated by sqlalchemy would generate a table lock which impacts other concurrent users who cannot perform any insert, update or delete operation on the table while the previous user is reading it. To avoid such table locks, I need to add the following string before the SELECT statement:

LOCK ROW FOR ACCESS
SELECT * FROM DATABASE.TABLE;

How can I override sqlalchemy select statements to add this string? Note that I have also raised the issue on GitHub here but did not find any solution so far: https://github.com/Teradata/sqlalchemy-teradata/issues/39

[Update]

When connecting to Teradata, I have added the statement provided below by dnoeth:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import scoped_session, sessionmaker
[...]
engine = create_engine('teradata://' + user + ':' + password + '@' + host + ':22/' + database)
db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
db_session.execute('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;')  # To avoid locking tables when doing select on tables

Base = declarative_base(cls=DeferredReflection)
Base.query = db_session.query_property()

But now I have the following error message when SQLAlchemy tries to read Teradata meta data:

sqlalchemy.exc.DatabaseError: (teradata.api.DatabaseError) (3932, '[25000] [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. ') [SQL: 'SELECT columnname, columntype, columnlength, chartype, decimaltotaldigits, decimalfractionaldigits, columnformat, nullable, defaultvalue, idcoltype \nFROM dbc.ColumnsV \nWHERE DatabaseName=? AND TableName=?'] [parameters: ('dev_migration_tool', 'migration_object_type')]

As a workaround, create a view with the additional lock row for access and use that in your query. — ULick
After logging on to Teradata submit SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; within your session once. Then the default lock will switch to ACCESS instead of READ. — dnoeth
@ULick I can't really do that because reflecting with SQLAlchemy Base class does not work on views. — Alexis.Rolland
@dnoeth, I will definitely test and see if I can do that with SQLAlchemy. Thank you — Alexis.Rolland
You seem to establish an ANSI-mode session (in Teradata mode it's autocomitted by default) and set autocommit=False. SET SESSION is considered a DDL statement, which must be commited. Adding COMMIT; should resolve the issue. — dnoeth

Alexis.Rolland Alexis.Rolland · Accepted Answer · 2018-04-04T02:59:56

When connecting to Teradata, I have added the statement provided in the comments above by dnoeth. Note that in ANSI-mode session, SET SESSION is considered a DDL statement, which must be commited. The following is required to avoid the error message triggered in the initial post above: db_session.commit()

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import scoped_session, sessionmaker
[...]
engine = create_engine('teradata://' + user + ':' + password + '@' + host + ':22/' + database)
db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
db_session.execute('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;')  # To avoid locking tables when doing select on tables
db_session.commit()

Base = declarative_base(cls=DeferredReflection)
Base.query = db_session.query_property()

Avoid Deadlocks Using Teradata and SQLAlchemy

1 Answers