Threads and Thread Synchronization

Database applications may use the simultaneous run of more control threads on the server. The applications may launch threads that are executed independently from the incoming client requests, may leave some part of work for these threads and then pick the results.

Each client request is processed in a separate thread on the server. If the request invokes a server stored procedure or trigger call, then there are two possibilities:

another thread is launched;
procedures that synchronize the thread operation are called.

Launching Another Thread

Another thread is launched by calling this command:

CALL DETACHED procedure_name( parameters ... )

This command creates a new, separate thread and executes the appropriate procedure with specified parameters in this thread. The calling thread then continues its previous action independently without waiting for the procedure to end. When this procedure ends, the transaction is commited and its thread is disposed.

A separate thread created by the CALL DETACHED command connects to the server with the same username as the calling thread, therefore having the same privileges. If the separate thread is launched by the system, it has administrator privileges. Launching a new thread doesn't consume further licences.

When a separate thread is running, the application that contains the called procedure is open.

The procedure called in a separate thread may have only input parameters. If it had output or input-output parameters, the operating system may crash if the real parameters cease to exist before the procedure is complete - the value copying into the output parameters is done at the very end of the procedure, after the last command. Usually the results of a procedure action are therefore stored in the database.

A running separate thread blocks some server resources (by its locks, open cursors etc.). It also prevents commencing of actions that require locking the server (consistency repairs, database compression etc.).

Executing Action Periodically in a Separate Thread

If a separate thread should execute certain action periodically, it's recommended to use the standard function Sleep. This function has one integer parameter that sets the count of milliseconds. This function halts the procedure execution until the specified time expires or until the server is terminated. If the function ends after the time expires, it returns TRUE, FALSE otherwise. A construction that ensures some periodical action each 15 seconds may look like this:

REPEAT
   the action that is repeated each 15 seconds
UNTIL NOT Sleep(15000)
END REPEAT;

The procedure ends when the SQL server is terminated.

If some action should be executed periodically but also on an external request, you may use waiting for a semaphore with a time limit (described below). The waiting ends when the time expires or another thread releases the semaphore.

Thread Synchronization Using Semaphores

The need to synchronize threads arises when some thread makes some preparations for another thread action and wants to wake that thread or waits for another thread to end its work. You can synchronize any two threads together, regardless if it's a separate thread or a thread executing a client's request, and regardless which client (or system) launched it.

Semaphores (marked by names) serve for thread synchronization. A thread that wants to work with a semaphore specifies the semaphore's name and receives a semaphore handle (an integer value). If two thread use the same semaphore number, they receive the same handle and may utilize it for synchronization.

A semaphore is created by the Create_Semaphore function, the input parameters set the semaphore name and the number of threads that the semaphore will be released for. A semaphore is deleted by the Close_Semaphore function, however it's actually deleted when all threads working with it delete it. The Release_Semaphore releases the semaphore, the atomic action of waiting for a released semaphore is executed by the Wait_For_Semaphore function.

Example:

Let the first thread prepare data for the second thread and waits for it to complete. The first thread may look like this:

DECLARE new_job, job_done INT;
SET new_job = Create_Semaphore("new job", 0);
SET job_done = Create_Semaphore("job done", 0);
...  // preparations for the second thread
CALL Release_Semaphore(new_job);
CALL Wait_For_Semaphore(job_done, -1);
... // job is done, process results
CALL Close_Semaphore(new_job);
CALL Close_Semaphore(job_done);

The second thread may look like this:

DECLARE new_job, job_done INT;
SET new_job = Create_Semaphore("new job", 0);
SET job_done = Create_Semaphore("job done", 0);
loop: LOOP
  CALL Wait_For_Semaphore(new_job, -1);
  IF end THEN LEAVE loop;
  END IF;
  ... // job executing
  CALL Release_Semaphore(job_done);
END LOOP;
CALL Close_Semaphore(new_job);
CALL Close_Semaphore(job_done);

The operation of the second thread is terminate by setting up an end condition and calling this command:

CALL Release_Semaphore(new_job);

This cooperation model allows the second thread to have higher privileges than the first one. If the first thread needs to execute actions that it's not privileged to, it has to ask the second thread for them.

Example:

Let there be a group of command that can be executed by only one thread at a time. This condition can be fulfilled by enclosing these commands like this:

DECLARE protected_section INT;
SET protected_section = Create_Semaphore("protected section", 1);
CALL Wait_For_Semaphore(protected_section, -1);
  ... // commands
CALL Release_Semaphore(protected_section);
CALL Close_Semaphore(protected_section);

If a thread forgets to delete a semaphore, the SQL server deletes it when the thread is terminated.

Transaction and Thread Cooperation

If a thread makes database changes and wants the changed data to be visible for other threads, it has to execute an explicit COMMIT. Otherwise, depending on the set level of the transaction isolation, the other thread will see the original data or halt until the termination of the transaction by the first thread.

If the SERIALIZABLE isolation level is set, then if the thread reads some data and wants to enable other threads to update the data, it has to execute an explicit commit, no matter who launched the thread.

Thread Information

All information concerning running threads (executed explicitly or by the system) can be gathered by calling the system query _iv_logged_users in the form

SELECT * FROM _iv_logged_users
WHERE detached OR worker_thread

or in the server's Monitor window. Information about controlling the thread runtime (including aborting the threads) can be found here.

Locks and Records Lockingn

602SQL Server

Client - Server Synchronization