Transaction X attempted to access an object created by transaction Y via JDBC

[Former Member]

I have jdbc code which talks to Sybase IQ. One of the thread is loading/update data into one Sybase IQ table say t1. Other threads will try to fire SQL query on table t1 after the first finished loading/updating data.

Write thread: Create table t1, load/update data into t1, commit to t1. Read thread(s): Read select query from t1

I am using JDBC and I am getting the following error as mentioned in here

Transaction 156593 attempted to access an object created by transaction 156608. -- (db_txnInfo.cxx 690) Sybase error code=21, SQLState=”QDA11”

I am not sure where is the problem I am commiting after every load. Every thread uses different connection read thread fires only select query then where is the problem how do I debug? Please guide. I am new to Sybase IQ and JDBC. Thanks in advance.

[saroj_bagai]

IQ only writes one writer,  you do need to finish one transaction commit and then start another transaction, If you are on IQ 16, then you can set  'Blocking' option

When BLOCKING is off, a transaction receives an error when it attempts a write operation and is blocked
by the read lock of another transaction.

When BLOCKING is on, any transaction attempting to obtain a lock that conflicts with an existing lock
held by another transaction waits until every conflicting lock is released or
until the blocking_timeout is reached. If the lock is not released within
blocking_timeout milliseconds, then an error is returned for the waiting transaction.

[Former Member]

Hi Sarorj thanks a lot for the reply. I am committing first transaction before starting another transaction and new transaction are just read/select query on the same table even jdbc connection is also different. Even I am putting Thread.sleep when first transaction completes so that it wont affect other transactions. Not able to understand why its a problem.

[saroj_bagai]

check with sp_iqconnection,  sp_iqlock,  first connection might be holding lock, as I said earlier if it is IQ 16, then try to set 'blocking ' option

[jong-kil_park]

In addition to above replies, I would suggest that you explicitly commit in the second session prior to running its statement.

The second transaction should have a new transaction id(higher number than the one of the first transaction) in order to access the table which was already modified by the first transaction.

Jerry

[Former Member]

You may have uncommited select statement on the same session!

[Former Member]

Hi Abhijit, thanks a lot for the response. I am commiting after executing sql query. Even I am closing the connection. Is it because of I am using prepared statements?

[Former Member]

Prepared statement should not be issue. Check iq message file for sequence of these transaction numbers. If not get request level logging to see whats happening with connections.