Concurrent Operations

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

Concurrent Operations That Return an 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	DELETE its associated primary table with deletable row(s).
	UPDATE its associated primary table.
	TRUNCATE its associated primary table.

SAP Sybase IQ also generates an error for a request by one transaction to alter a table to add a foreign key or drop a foreign key while there are old versions of the foreign table, the primary table, or both, in use by other transactions.

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

Simultaneous load or insert on one or more foreign tables and the shared primary table.
Simultaneous load or insert on foreign tables and delete, or update, or truncate on another one or more foreign tables.
Simultaneous delete, or update, or truncate on two or more foreign tables, even if sharing the same primary table.
Simultaneous delete, or update, or truncate on foreign tables and delete, or update, or truncate on the shared primary table.
ALTER TABLE ADD foreign key or DROP foreign key if no transaction is using any old version(s) of foreign/primary table and these unused old version(s) will be dropped as part of the ADD/DROP foreign key operation.

Concurrent Operations on Foreign and Primary Tables

The table-level versioning of SAP Sybase IQ guarantees consistent referential integrity checks while allowing concurrent load, or insert, or update operations on the foreign table and LOAD/INSERT operations on the primary table.

SAP Sybase IQ also verifies that there are no deleted old values in a foreign table when a transaction requesting DELETE or UPDATE starts. This provides consistent referential integrity checking during a concurrent delete operation on a foreign table and a delete or update operation on a PRIMARY Table.

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.

This table shows which operations on both tables (foreign and primary) should be allowed and which return an error. Information in the table applies only to enforced foreign keys and candidate key.

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	N/A	N/A	N/A	N/A	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 conflict if one transaction loads foreign key columns while another updates unassociated candidate key columns on one of its associated candidate tables.

Note: For 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 read locks on the tables are released, you cannot alter or drop the tables that have foreign keys used as join conditions or grouping columns. You can, however, load, insert, delete, and update these tables while the query is running.