imavo
commented
2 years ago Your symptom has nothing to do with sqlalchemy, or autocommit, or ibm_db_sa, or ibm_db, or python.
Instead, Db2-LUW is working as designed , specifically for the statementTRUNCATE ...IMMEDIATE. The IMMEDIATE clause causes Db2-LUW to process the statement immediately and it cannot be undone and is therefore not impacted by the autocommit setting.
Study the documentation for more details. https://www.ibm.com/docs/en/db2/11.5?topic=statements-truncate
This ticket should be closed.
By default with sqlalchemy has autocommit enabled. However, this is not working with the ibm_db_sa interface when a "truncate table command is executed"
Test case:
sqlalchemy.version '1.3.23'
Create the engine
engine = create_engine(sqla_url, pool_size=10, max_overflow=20) conn = engine.connect()
Create the table if it does not exist and truncate to ensure that it is empty
print("Creating table if not exists") conn.execute("create table if not exists johannes.mbike2(manufacturer varchar(50), model varchar(50), year int)") db2pd("-db sample -wlock -lock")
Creating table if not exists
RETURN CODE None Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:08 -- Date 2021-08-30-14.32.05.398276
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:08 -- Date 2021-08-30-14.32.05.398290
Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
print("Truncating table") conn.execute("truncate table johannes.mbike2 immediate") db2pd("-db sample -wlock -lock")
Truncating table
RETURN CODE None Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:14 -- Date 2021-08-30-14.32.11.443112
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:14 -- Date 2021-08-30-14.32.11.443126
Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID 0x00007F83EAC22980 3 00000500033D000060E95EFBC3 CatCacheLock ..S G 3 2 0 0x00000000 0x40000000 0 0x00007F83EAC22B80 3 0000050003003D000000000052 RowLock ..X G 3 2 0 0x00200000 0x40000000 0 0x00007F83EAB6BF00 3 0200000000000000000000006D ExtentMoveLock ..S G 3 1 0 0x00200100 0x40000000 0 0x00007F83EAC22C00 3 010000000100000001004055D6 VarLock ..S G 3 1 0 0x00000000 0x40000000 0 0x00007F83EAC22480 3 00000500000000000000000054 TableLock .IX G 3 4 0 0x00202000 0x40000000 0 0x00007F83EABC8180 3 02001100000000000000000054 TableLock ..Z G 3 1 0 0x00202000 0x40000000 0
From this there is a row lock left in the SYSIBM.SYSTABLES table: select substr(tabschema,1,6) as schema, substr(tabname,1,10) as table, tbspaceid, tableid from syscat.tables where tableid = 5 and tbspaceid =0
SCHEMA TABLE TBSPACEID TABLEID
SYSIBM SYSTABLES 0 5
1 record(s) selected.
and an exclusive lock on the base table: db2 "select substr(tabschema,1,9) as schema, substr(tabname,1,10) as table, tbspaceid, tableid from syscat.tables where tableid =17 and tbspaceid =2"
SCHEMA TABLE TBSPACEID TABLEID
JOHANNES MBIKE2 2 17
However, when autocommit is explicitly declared for the engine as a work around, then the truncate table command does not hold any locks:
engine = create_engine(sqla_url, pool_size=10, max_overflow=20) conn = engine.connect().execution_options(autocommit=True) conn.execute("truncate table johannes.mbike2 immediate") db2pd("-db sample -wlock -lock")
Truncating table RETURN CODE None Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:19:56 -- Date 2021-08-30-14.42.53.906285
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:19:56 -- Date 2021-08-30-14.42.53.906298
Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID