Versioning and locks

In Sybase IQ no two users can modify data in the same table at the same time. When you try to do this you will get an error in your application, as well as in the IQ message file. In IQ you can also run into problems when your transaction tries to modify something but hits an object or data that has been created after you started your transaction. Confusing? Read on and you will understand.

Transactions and Versioning

Conflicting transactions

To see how IQ works with transactions and versioning open two sessions to IQ and run the following set of commands:

Session-1: create table t1(a int)
Session-1: begin tran
Session-2: create table t2(a int)
Session-1: insert into t2 values(1)

The following error is then raised:

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

In the IQ message file the error is also reported:

I. 01/07 14:55:58. 0000000563 Exception Thrown from db_txnInfo.cxx:690, Err# 0, tid 478 origtid 478
I. 01/07 14:55:58. 0000000563    O/S Err#: 0, ErrID: 1025 (db_catalogException); SQLCode: -1000011, SQLState: 'QDA11', Severity: 14
I. 01/07 14:55:58. 0000000563 [20671]: Transaction 156593 attempted to access an object created by transaction 156608. 
-- (db_txnInfo.cxx 690)

This error happened because IQ works with table level versioning. Every transaction in IQ gets a number and that transaction can not deal with data from transactions that have started later (have a higher number). In the example, table t2 is created within a transaction with a higher number than the transaction in session 1 that tried to insert a row into table t2. Under normal situations you will not often hit this problem, but it can happen when you run multiple sessions in IQ updating data at the same time.

Coding around conflicting transactions

To make an application resilient for this you can use error trapping, as in the following example. Again, open two sessions to IQ and run the following set of commands:

Session-1: create table t1(a int);
Session-1: begin tran;
Session-2: create table t2(a int);

Then run a command in Session-1 that traps for the error and give a message. Run the following piece of code in one batch.

begin
  declare tran_error exception for SQLSTATE 'QDA11';
  insert into t2 values(1);
  message 'Everyting ok, no conflicting transaction' to client;
  exception
    when tran_error then message 'You hit a conflicting transaction' to client;
    when others then resignal;
end;

Locking

Yet to be written.