Schema locks are used to serialize changes to a database schema, and to ensure that transactions using a table are not affected by schema changes initiated by other connections. For example, a schema lock prevents an ALTER TABLE statement from dropping a column from a table when that table is being read by an open cursor on another connection.
There are two classes of schema locks:
Shared locks The table schema is locked in shared (read) mode.
Exclusive locks The table schema is locked for the exclusive use of a single connection.
A shared schema lock is acquired when a transaction refers directly or indirectly to a table in the database. Shared schema locks do not conflict with each other; any number of transactions can acquire shared locks on the same table at the same time. The shared schema lock is held until the transaction completes via a COMMIT or ROLLBACK.
Any connection holding a shared schema lock is allowed to change table data, providing the change does not conflict with other connections.
An exclusive schema lock is acquired when the schema of a table is modified, usually through the use of a DDL statement. The ALTER TABLE statement is one example of a DDL statement that acquires an exclusive lock on the table prior to modifying it. Only one connection can acquire an exclusive schema lock on a table at any time—all other attempts to lock the table's schema (shared or exclusive) will either block or fail with an error. This means that a connection executing at isolation level 0, which is the least restrictive isolation level, will be blocked from reading rows from a table whose schema has been locked in exclusive mode.
Only the connection holding the exclusive table schema lock can change the table data.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|