Controlling concurrent operations

The referential integrity feature of Sybase IQ restricts concurrent updates or deletes on a primary table during loads or inserts on a foreign table.

Table 9-1: Concurrent operations that return an IQ error

First request

Request of overlapping transaction

Request by one transaction for LOAD/INSERT/UPDATE/ ALTER TABLE ADD foreign key/ ALTER TABLE DROP foreign key to any foreign table

to DELETE its associated primary table with deletable row(s).

to UPDATE its associated primary table.

to TRUNCATE its associated primary table.

Sybase IQ also generates an error for a request by one transaction to ALTER TABLE ADD foreign key or DROP foreign key while there are old version(s) of foreign table and/or primary table in use by other transactions.

For both enforced and unenforced foreign key and primary key, Sybase IQ allows:

Concurrent operations on Foreign and Primary Tables

The table level versioning of Sybase IQ guarantees consistent referential integrity checks while allowing concurrent LOAD/INSERT/UPDATE operations on the foreign table and LOAD/INSERT operations on the primary table.

Sybase IQ also verifies that deleted old values do not exist in a foreign table when a transaction requesting DELETE or UPDATE starts. This provides consistent referential integrity checking during concurrent DELETE on a foreign table and DELETE/UPDATE on a PRIMARY Table.

To understand concurrent operations on foreign and primary tables, assume that there are two foreign key constraints among two foreign tables, ftab1 and ftab2, and one primary table, ptab. Assume that foreign key ftab1(fk1,fk2) references candidate key ptab(pk1,pk2). Foreign key ftab2(fk1,fk2) references the same candidate key. Candidate key ptab(pk1,pk2) can either be a primary key or a unique constraint.

Table 9-2 shows which operations on both foreign table and primary table should be allowed and which return an error. Table 9-2 applies only to enforced foreign keys and candidate key.

Table 9-2: Concurrent DML on Foreign and Primary Tables

LOAD or INSERT ftab1

DELETE/ TRUNCATE TABLE ftab1

UPDATE ftab1 (fk1,fk2)

Populate new index non-FK ftab1 (fk1,fk2)

ADD FK ftab1 (fk1 fk2)

DROP FK ftab1 (fk2, fk2)

LOAD ftab2

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

LOAD ptab

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

INSERT ftab2

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

INSERT ptab

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

DELETE ftab2 TRUNCATE TABLE ftab2

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

DELETE ptab TRUNCATE TABLE ptab

Error

Allowed

Error

Allowed

Error

Error

UPDATE ftab2(fk1,fk2)

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

UPDATE ptab (pk1,pk2)

Error

Allowed

Error

Allowed

Error

Error

Populate new index

Allowed

Allowed

Allowed

Allowed

Allowed

Allowed

QUERY (old version of ftab1/ptab in use with or without (fk1,fk2))

Allowed

Allowed

Allowed

Allowed

Error

Error

No old version of ftab2 in use

Not Applicable

Not Applicable

Not Applicable

Not Applicable

Allowed (drop all unused old versions of ftab1)

Allowed (drop all unused old versions of ftab1)

Concurrency conflict occurs if one transaction loads foreign key columns while another updates associated candidate key columns. There is no concurrency conflict if one transaction loads foreign key columns while another updates non-associated candidate key columns on one of its associated candidate tables.

NoteFor efficient performance, a query on union all views opens the tables referred to by those columns used as join keys or group by columns. Until the transaction commits and the read locks on the tables are released, you cannot alter or drop the tables whose foreign keys are used as join conditions or grouping columns. You can, however, load, insert, delete, and update these tables while the query is running.