In Sybase ETL 4.8, you can use the Lock Table statement to acquire exclusive locks on a set of Sybase IQ tables, and prevent these tables from being accessed or modified by other concurrent transactions. To lock a table in EXCLUSIVE mode, you must be the table owner or have DBA authority. When an exclusive table lock is applied, no other transaction can execute queries, perform updates of any kind, or any other action against the locked table.
When Lock Table statement locks the tables of the first project, the second project does not fail; instead it waits for a user-defined time before trying again. This ensures the projects are not required to wait for a manual restart or for the next scheduled execution. For example, if project ‘A’ is writing data to the target table and project ‘B’ is trying to acquire the exclusive lock on the same table, project 'B' is made to wait for the specified wait time before it can acquire the lock. The wait time is the maximum blocking time for acquiring the EXCLUSIVE lock. When a time argument is given, the data update statements (DML) wait for the specified period of time if the target tables are locked. Otherwise the server waits indefinitely until a lock is available or an interruption occurs.
The syntax for applying an exclusive table lock is:
LOCK TABLE table-name WITH HOLD IN EXCLUSIVE MODE WAIT [time]
Where:
table-name — Must
be a base table, not a view
WITH HOLD — Holds
the lock until the end of the connection. Otherwise, releases the
lock when current transaction is rolled back or ends.
EXCLUSIVE mode — Other
transactions cannot execute queries, updates, or any other transaction
against the table. Requires DBA authority or ownership of the table.
This statement blocks or delays other transactions that may require access to the table. LOCK TABLE statement allows direct control over concurrency at a table level, independent of isolation level.
The following Sybase IQ components supports the exclusive lock table statement:
Destination - all data sink components, except “Text Data Sink”, supportsLock Table.
Loader - all the loader components.
Staging - DB Staging component.
You can set two additional parameters for these components, which, when writing to the Sybase IQ database, appropriately lock the Sybase IQ table before writing the data.
IQ Lock Table in Exclusive Mode:
Not selected - does not lock the target table in EXCLUSIVE mode.
Selected - locks the target table in EXCLUSIVE mode.
Wait Time for IQ Lock Table:
hh:nn:ss.sss - specifies maximum blocking time for an exclusive lock.
For example:
00:06:00.000 -
sets the maximum blocking time for a exclusive lock to 6 minutes.
00:00:00.000 -
gets the exclusive lock immediately without any waiting.
“ ” (empty
string) - waits infinite until getting the exclusive lock.
If the Lock Table mode is selected, Sybase ETL generates a Lock Table statement and executes it before any other SQL statements are executed on the table.
For detailed information about the Lock Table feature in Sybase IQ, see Chapter 6, “SQL Statements” in the Sybase IQ 12.7 Reference Manual.
For information about new parameters in the ETL components for Sybase IQ, see Chapter 5, “Components” in the Sybase ETL 4.8 Users Guide.
