|
Creates or replaces a semaphore and establishes the initial value for its counter. A semaphore is a locking mechanism that uses a counter to communicate and control the availability of a resource such as an external library or procedure.
CREATE [ OR REPLACE | TEMPORARY ] SEMAPHORE [ IF NOT EXISTS ] [ owner.]semaphore-name [ START WITH initial-count ]
The owner of the semaphore. owner can also be specified using an indirect identifier (for example, `[@variable-name]`).
The name of the semaphore. Specify a valid identifier in the CHAR database collation. semaphore-name can also be specified using an indirect identifier (for example, `[@variable-name]`).
Use this clause to overwrite (update) the definition of a permanent semaphore of the same name, if one exists.
If the OR REPLACE clause is specified, and a semaphore 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 semaphore.
Do not use this clause with the OR REPLACE clause.
Use this clause to create a semaphore only if it doesn't already exist. If a semaphore exists with the same name and same lifespan (permanent or temporary), then nothing happens and no error is returned.
You cannot use this clause with the OR REPLACE clause.
Use this clause to specify the initial value for the semaphore counter. If this clause is not specified, then initial-count is set to 0.
initial-count can be specified using a variable (for example, START WITH @initial-count).
If you set initial-count to NULL, or if it is set to a variable and the variable value is NULL, the behavior is equivalent to not specifying the clause.
The CREATE SEMAPHORE statement creates a semaphore and establishes a counter for it. Each time a NOTIFY SEMAPHORE statement is executed, the counter for the associated semaphore is incremented. Each time a WAITFOR SEMAPHORE statement is executed, and assuming the current count is a positive integer, the counter for the associated semaphore is decremented.
Permanent and temporary mutexes and semaphores share the same namespace, therefore you cannot create two of these objects with the same name. 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 semaphore definitions persist across database restarts. However, their count returns to initial-count after a restart.
A temporary semaphore persists until the connection that created it is terminated, or until an explicit DROP operation is performed. If another connection is waiting for a temporary semaphore and the connection that created the temporary semaphore is terminated, then an error is returned to the waiting connection.
When replacing (OR REPLACE clause) a permanent semaphore, the old semaphore is deleted, and all connections waiting for the semaphore are notified.
If the OR REPLACE clause is specified, and a permanent semaphore with that name exists and connections are blocked waiting for the semaphore, the semaphore is still replaced. In this case, the waiting connections are unblocked and an error is returned to them indicating that the semaphore has been dropped. There is one exception however. If the replacement semaphore definition has identical settings, there is no impact to waiting connections.
You must have the CREATE ANY MUTEX SEMAPHORE or CREATE ANY OBJECT system privilege.
Automatic commit, but only for permanent semaphores.
Not in the standard.
The following statement creates a semaphore called license_counter and sets its counter to 3:
CREATE SEMAPHORE license_counter START WITH 3;