-1

My application is using hibernate 3.6.10 and database is db2. Isolation level is default i.e 2 (Read Uncommitted). if i initiate a transaction to insert/update in table, i can't read data from that table until the transaction is committed. All i want is that hibernate let me read the data.

I ran the select query on that table in db visualizer during the transaction it returned the records during transaction. Also the default release mode is after_transaction. I tried to change it to auto by adding following property:

auto

but it didn't work. below is my hibernate.cfg.xml

        <property name="hibernate.connection.driver_class">
            com.ibm.db2.jcc.DB2Driver
        </property>
        <property name="hibernate.connection.url">
                jdbc:db2://ip/dbname
        </property>
        <property name="hibernate.default_schema">dbname</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.c3p0.max_size">24</property>
        <property name="hibernate.c3p0.min_size">7</property>
        <property name="hibernate.c3p0.acquire_increment">2</property> 
        <property name="hibernate.connection.release_mode">auto</property> 
        <property name="dialect">org.hibernate.dialect.DB2Dialect</property>
        <property name="show_sql">false</property>
        <property 
name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
<property name="hibernate.cache.use_second_level_cache">false</property>

<property name="hibernate.cache.use_query_cache">false</property>
mustaccio
  • 18,234
  • 16
  • 48
  • 57
Abdulrauf hashmi
  • 58
  • 8
  • Looks like the *actual* isolation level is not Read-Uncommitted. So *prove* it by getting the metadata and looking at the current isolation. You should also mention the exact version of the jdbc driver, along with the target Db2-server platform (z/os, i-series, linux/unix/windows). – mao Aug 21 '19 at 09:02
  • @mao sorry it was a typo, the default isolation level is read committed. db2 version is 9.7.0.4 and driver is db2jcc4.jar and db2jcc_license_cu.jar. – Abdulrauf hashmi Aug 21 '19 at 09:13
  • Your question title states that the whole table is being locked. This is unlikely, unless your transaction is triggering lock-escalation. Debug your app. Ask the DBA (if one exists) to help you to verify both the *actual* isolation level and the *actual* locks that are being taken. Things are often not what you assume. – mao Aug 21 '19 at 11:48
  • @mao i checked the isolation by following method sesion.connection().getTransactionIsolation() and it says the isolation level is 2. I also checked db2 isolation level by running following sql command: 'VALUES CURRENT ISOLATION' and it says cs. lock on table doesn't stay forever but till the transaction is committed. – Abdulrauf hashmi Aug 21 '19 at 13:06

1 Answers1

0

From the db2 server perspective, there are a few things you can explore to narrow down the types of locks (and applications) involved in your lock-wait condition:

1) You can gather details on all the lock-wait conditions in the on the database in real-time through monitoring tools like "db2 call monreport.lockwait". You'll need to time this collection right so as to collect the data when your application is actively in lock-wait state. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0056374.html

2) You can gather details on all the lock-wait conditions in the on the database in real-time through other monitoring tools like "db2pd -db -wlocks -transactions". You can examine the TranHdl value in the -transactions output to identify the TranHdl for your application's connection to the database, and then examine the -wlocks information for your TranHdl to see the actual locks involved in the lock wait condition. Hint, you can use the MON_FORMAT_LOCK_NAME function to format the lockname to determine which database tables the lock is associated with). https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/t0055234.html

3) You can enable and event-monitor to collecting lock-wait details. You can explore that here: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/t0055093.html

DavidSciDb2
  • 1