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

In Sybase IQ only a single session can write to a particular table at any given time. There can be multiple update sessions going at the same time, but each session should update a different table. When you try to break this rule the following error message is raised:

Msg 8405, Level 21, State 0:
SQL Anywhere Error -210: User 'Joe' has the row in 'myTable' locked

In the IQ message log the following is raised:

I. 02/16 16:29:53. 0000000898 Txn 114153 0 114153
I. 02/16 16:29:53. 0000000898    sqlcode -210
I. 02/16 16:29:53. 0000000898    string_id 2169
I. 02/16 16:29:53. 0000000898    _sqlstate 67792968
I. 02/16 16:29:53. 0000000898    odbc30state 67108865
I. 02/16 16:29:53. 0000000898    sybcode 8405
I. 02/16 16:29:53. 0000000898    severity 21
I. 02/16 16:29:53. 0000000898    odbc20state 67108865
I. 02/16 16:29:53. 0000000898 Exception Thrown from db_catalog.cxx:717, Err# 0, tid 1979 origtid 1979
I. 02/16 16:29:53. 0000000898    O/S Err#: 0, ErrID: 5122 (st_sacbexception); SQLCode: 0, SQLState: '00000', Severity: 10
I. 02/16 16:29:53. 0000000898 [2000]: 
I. 02/16 16:29:53. 0000000898 Rbck
I. 02/16 16:29:53. 0000000898 PostRbck
I. 02/16 16:30:10. 0000000950 Txn 114155 0 114155
I. 02/16 16:30:10. 0000000950 Cmt 114156
I. 02/16 16:30:10. 0000000950 PostCmt 0

In Sybase IQ 15 the "lock table" command can now be used to request a lock on a table and when the lock cannot be granted just wait until it can. An example of this is:

lock table <table-name> in write mode wait "00:10:00"

The commands request a lock for a table and when it is granted no other processes can write to the same table. When the lock cannot be granted the lock command will wait 10 minutes as specified with "00:10:00".

By preceeding each insert/update/delete or load table command by a "lock table" the error message can be easily prevented when the application requires it.