602SQL Documentation Index  
<­Name, Address and Identity of a 602SQL Server UpSQL Server Operation Setting Memory Allocations­>

Server Runtime Controlling and Runtime Restrictions

Clients may connect to a working SQL server. Besides that, separate threads executed by other clients or the server itself (e.g. in the _on_server_start procedure) may be running on the server.

You can get an overview of logged threads and client by calling the _iv_logged_users system query or from the Clients and threads tab in the Monitor window.

Locking and Unlocking the Server

There are some actions that require that no client or thread is working on the server - e.g. table modification when upgrading data structures or database integrity maintenance. You have to lock the server before executing these actions. When you lock a server:

Locking and unlocking is done by the Lock_server and Unlock_server functions. A lock can also be set by checking the Client access is locked checkbox in the Monitor window.

Terminating Logged Client Activity

If there are some users logged in, there are more ways how to terminate their activity:

  1. A prompt to disconnect from the server may be sent to the clients. This should be the primary way how to treat interactive clients. Noninteractive clients (client programs) or clients run be some user who is not directly at the computer won't however respond to this prompt.

    The Message_to_clients function is user to send this message to clients. It can be sent also from the Control Panel (System / Tools / Message to clients).

  2. If a client or a thread performs a longlasting server operation, this operation may be interrupted - it results in an error then and the changes made in last transaction are rolled back.

    The Break_user function serves for this interruption as well as the Break the client's operation action on the Clients and threads tab of the Monitor window. It's necessary to keep in mind, that client applications may not be prepared for an error result and therefore they may react inadequately.

  3. A logged client, who is idle, can be disconnected ungracefully. If a client has a transaction underway, the changes will be rolled back.

    The Kill_user function and the Disconnect the clients from the SQL server action on the Clients and threads tab of the Monitor window serve for ungraceful logoff. Not every client program will react properly when disconnected in this way.

  4. If a client is connected and waits inside some function, then the waiting may be interrupted as described further.

Terminating Waiting Clients and Threads on the Server

Clients and separate threads may wait on the server in the Wait_for_semaphore or Sleep functions. Moreover, clients may be waiting in the Wait_for_event function as well.

This wait may be terminated by an external action. The results of this action are that

  1. all specified executed functions are terminated and the functions return results for premature termination (the same as when terminating SQL server),
  2. the server is shifted to a state when each consecutive call to these functions ends immediately,
  3. the procedure _on_worker_stop is called (without parameters) in each application that contains this procedure.

This action can be executed by unchecking the Background processes enabled in the Monitor window or by calling the Operation_limits function with the WORKER_STOP parameter.

This action may be executed only be a configuration administrator. The _on_worker_stop procedure call is run under the configuration administrator account. Therefore the config_admin group has to be granted all privileges needed to execute this procedure and to all objects this procedure affects.

If this mechanism is to serve successfully for thread termination, then these threads have to test the results of the Wait_for_semaphore and Sleep functions. If the result signals termination of server operation, these threads must terminate immediately.

Restoring Thread Operation

Many applications need one or more separate threads for their operation. These threads are usually executed on the server startup.

If the threads are terminated by the tools described above, they can be restored (and executed again) in this way:

  1. a stored procedure _on_worker_restart is created in each application that uses threads,
  2. by checking the Background processes enabled in the Monitor window or by calling the Operation_limits function with the WORKER_RESTART parameter are all these procedures executed.

This action may be executed by the configuration administrator only. The _on_worker_restart procedure call is run under the configuration administrator account. Therefore the config_admin group has to be granted all privileges needed to execute this procedure and to all objects this procedure affects.

If there's a risk that some thread isn't terminated, then it's up to the _on_worker_restart procedure author to recognize such situation and not run the thread again.

If the separate threads were terminated by the action described above, the they have to be re-executed in the described way. Otherwise the server will remain in a state where each wait function ends immediately and all executed separate threads will terminate immediately.

Multiple Processors

The 602SQL server uses one thread per connected client (plus possibly some detached threads for special tasks). The operating system can assign threads to processors but cannot use more than one processor for one thread. Supposing that the computer does not work on other tasks in the some time the main conclusion is: Multiple processors are NOT an advantage when serving single client. The processing time of a query does not depend on the number of processors.

The SQL server uses multiple processors only when simultaneously working for multiple clients.

<­Name, Address and Identity of a 602SQL Server UpSQL Server Operation Setting Memory Allocations­>