Psap: /* Locking */

2010-02-16T16:05:23Z

‎Locking

Psap at 16:13, 9 January 2008

2008-01-09T16:13:47Z

New page

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.

[[Category:IQ]]

@@ Line 46: / Line 46: @@
 ==Locking==
-Yet to be written.
+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:
 [[Category:IQ]]

Versioning and locks - Revision history

Psap: /* Locking */

Psap at 16:13, 9 January 2008