0

I have some python code that I am trying to use to read uncommitted from my database in parallel using sqlalchemy and modin. I have tried calling the function as: df = pd.read_sql("select * from my_table", uri_string, params={'isolation_level': 'READ UNCOMMITTED'}). However, I am seeing the generated queries submitted without an isolation level to my database. Has anyone been able to inject the isolation level without creating an sqlalchemy engine before calling read_sql?

EDIT: Unlike pandas, modin's implementation allows read_sql to run in parallel. Part of that implementation means that whatever query I write will be wrapped into several subqueries like select * from (select * from my_table) limit 100 offset 200;. Therefore, statement level isolation is off the table without adjusting this implementation. Further, this implementation requires an sqlalchemy URI string rather than an engine or connection which can be serialized by many workers. Therefore, I would need to know how to inject the isolation level parameter within the URI string if I were to set the isolation level from the connection string.

0.0.1.dan0
  • 1
  • 1
  • have you tried `select * from my_table WITH UR` (statement level isolation level). – mao Jul 23 '21 at 18:42
  • Edited. Statement level isolation is not an option given the implementation of modin. – 0.0.1.dan0 Jul 26 '21 at 13:18
  • please edit your question to show your current URI (just mask, but do not remove, any confidential stuff). ADDITIONALLY, specify the Db2-server platform (z/os , i series , cloud, linux/unix/windows) – mao Jul 26 '21 at 13:22

1 Answers1

0

Every database transaction for Db2-LUW has an isolation level regardless of whether or not you make it explicit - there is always some default. But if you are connecting to Db2-for-i (as/400) this might not not true.

You make a choice as to how and where you change the isolation level .

For occasional queries you can use statement level isolation (as long as the tooling allows it), where the SELECT statement itself has an optional clause appended to specify the isolation for that statement only. For example select whatever from table where... with UR (uncommitted read).

If you want all dynamic SQL statements to have the same isolation level (where that has a meaning), then you can specify the isolation when you connect to the database. For python, (i.e. cpython) which uses the CLI interface, you use a CLI setting either on the connection string ;TxnIsolation=1; (for UR), or via the data source definition in db2dsdriver.cfg or db2cli.ini (if using) with the parameter IsolationLevel=.... More details here

If you call (directly or indirectly) any Db2 routines with static packages that are bound with a specific isolation level then this will override any other settings you may have previously set, so be aware.

mao
  • 11,321
  • 2
  • 13
  • 29
  • Thank you for the detailed response! I was hoping for the option to set the isolation level at the runtime of my script. Though, sqlalchemy's URI string format is a bit different than the tradition connection string you may use with pyodbc, for example. Any thoughts on how to set the parameter from an sqlalchemy URI string? If not, I could for sqlalchemy to use pyodbc. – 0.0.1.dan0 Jul 26 '21 at 13:31
  • @0.0.1.dan0 please edit your question to show your current Db2-server platform (z/os, i series , linux /unix /windows, cloud) and your current URI (with any confidential detailes masked but not removed). – mao Jul 26 '21 at 13:32