Schema Lock Wait Queue(-schlockwq)

Database Administration

Reference : Database Startup Parameters : Alphabetical listing of database startup parameters : Schema Lock Wait Queue(-schlockwq)

Operating system and syntax	UNIX / Windows	-schlockwq
Use with	Maximum value	Minimum value	Single-user default	Multi-user default
DBS	–	–	–	3

Schema Lock Wait Queue (-schlockwq) alters the schema locking algorithm.

When the database broker is started with -schlockwq specified, the schema lock scheduler forces a shared schema lock request to wait when an exclusive schema lock request is queued. Once an exclusive schema lock request is queued, it appears to all subsequent schema lock requests that an exclusive schema lock has been granted (meaning all subsequent schema lock requests will queue). Without -schlockwq, shared locks continue to be granted, even though an exclusive lock is queued.

With -schlockwq, wake up of users waiting for a schema lock is performed in the order they were received. Without -schlockwq, all users are awoken whenever any schema lock conflict is resolved, causing a race to see which user gets the available lock. When running with -schlockwq enabled at broker startup, only those users allowed to get the schema lock will be awoken.

All existing lock strength conflicts and properties are unchanged, as follows:

Exclusive locks conflict with shared and exclusive locks for other users.

Share locks may coexist with other share locks but conflict with exclusive locks of other users.

An individual user may possess at most one shared lock and one exclusive lock concurrently.

An individual user may never possess more than one lock of the same lock strength (i.e. two concurrent share locks for the same user is prohibited).

An individual user may only ever have one entry on the wait queue.

These behaviors are inherently supported by each of the language engines and enforced by the storage engine.

The -schlockwq parameter has no impact when the database is started in single-user mode.