|
Creates or replaces a mutex (lock) that can be used to lock a resource such as a file or a procedure.
CREATE [ OR REPLACE | TEMPORARY ] MUTEX [ IF NOT EXISTS ] [ owner.]mutex-name [ SCOPE { CONNECTION | TRANSACTION } ]
The owner of the mutex. owner can also be specified using an indirect identifier (for example, `[@variable-name]`).
The name of the mutex. Specify a valid identifier in the CHAR database collation. mutex-name can also be specified using an indirect identifier (for example, `[@variable-name]`).
Use this clause to overwrite (update) the definition of a permanent mutex of the same name, if one exists.
If the OR REPLACE clause is specified, and a mutex with this name is in use at the time, then the statement returns an error.
You cannot use this clause with the TEMPORARY or IF NOT EXISTS clauses.
Use this clause to create a temporary mutex.
Do not use this clause with the OR REPLACE clause.
Use this clause to create a mutex only if it doesn't already exist. If a mutex exists with the same name, then nothing happens and no error is returned.
You cannot use this clause with the OR REPLACE clause.
Use this clause to specify whether the mutex applies to a transaction (TRANSACTION), or the connection (CONNECTION). If the SCOPE clause is not specified, then the default behavior is CONNECTION.
Permanent and temporary mutexes and semaphores share the same namespace; therefore, you cannot create two of these objects with the same name and owner. Use of the OR REPLACE and IF NOT EXISTS clause can inadvertently cause an error related to naming. For example, if you have a permanent mutex, and you try to create a temporary semaphore with the same name, an error is returned even if you specify IF NOT EXISTS. Similarly, if you have a temporary semaphore, and you try to replace it with a permanent semaphore with the same name by specifying OR REPLACE, an error is returned because this is equivalent to attempting to create a second object with the same name.
Permanent mutex definitions persist across database restarts. However, their state information (locked or released), does not.
A temporary mutex persists until the connection that created it is terminated, or until the mutex is dropped using a DROP MUTEX statement. If another connection is waiting for a temporary mutex and the connection that created the temporary mutex is terminated, then an error is returned to the waiting connection indicating that the mutex has been deleted.
CONNECTION scope mutexes are not automatically released other than when the connection is terminated.
You must have the CREATE ANY MUTEX SEMAPHORE or CREATE ANY OBJECT system privilege.
Automatic commit, but only for permanent mutexes.
Not in the standard.
The following statement creates a connection scope mutex called protect_my_cr_section to protect a critical section of a stored procedure.
CREATE MUTEX protect_my_cr_section SCOPE CONNECTION;