cfkoh
commented
6 years ago Here is the scenario when this happened:
0: jdbc:db2://dashdb-entry-yp-syd01-01.servic> SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME"; Error: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=3.70.4 (state=40001,code=-911)
idadb.exists_table_or_view could fail with deadlock; if another user create a table but never commit.
1) Python code: db_tables=idadb1.show_tables(show_all=False) Actual SQL in backend: SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U')AND(TABSCHEMA= 'DASH5421') ORDER BY "TABSCHEMA","TABNAME"
2) Python code: db_tables=idadb1.show_tables(show_all=True) Actual SQL in backend: SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME"
In dashdb-entry-yp-syd01-01.services.au-syd.bluemix.net ; which is a multi-tenants, multi-users environment. There is another application , did a create table and never commit. Hence the case #2 above is causing dead lock, since it could not complete.
Here will be the errors from Python program:
Traceback (most recent call last):
File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 505, in exists_table return self._exists(tablename,['T']) File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 1507, in _exists tablelist = self.show_tables(show_all=True) File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 384, in show_tables cache = self._retrieve_cache("cache_show_tables") File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 2086, in _retrieve_cache self._check_connection() File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 2076, in _check_connection raise IdaDataBaseError("The connection is closed") IdaDataBaseError: ibmdbpy::IdaDataBaseError: The connection is closed
This query should work better: SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME" with UR;
In actual scenario, we have to get into the system and abort the user who is causing the deadlock (create table but did not commit). But this could happen again, putting it here to see of code fix is possible ?
from, cfkoh@au1.ibm.com