SQL Anywhere threading

To understand the SQL Anywhere threading model, you must understand the basic terminology and concepts of threading and request processing:

  • Request   A request is a unit of work, such as a query or SQL statement, that is sent to the database server over a connection. The lifetime of a request spans the time from when the request is first received by the database server to the time that the last of the results are returned, cursors are closed, or the request is canceled.

  • Task   A task is a unit of activity that is performed within the database server, and is the smallest unit of work that is scheduled by the database server. Within the database server, each user request becomes at least one task, and possibly more if intra-query parallelism is involved. In addition to user requests, the database server can also schedule its own tasks to perform internal tasks, such as running the database cleaner or processing timers. The maximum number of active tasks that can execute concurrently depends on the size of the worker pool within the kernel. If a user request task arrives at the database server and a worker is assigned to it, the ActiveReq server property is incremented by 1. Once the request completes, the ActiveReq server property is decremented by 1. If, however, there were no available workers to execute the user request task, then the task is queued for execution and the UnschReq server property is incremented by 1. When the task is dequeued for execution because a worker is now available, the UnschReq server property is decremented by 1, and the ActiveReq property is incremented by 1. See UnschReq server property and ActiveReq server property.

  • Worker   A worker is an abstraction of an executing thread-of-control within the database server kernel. On Windows and Linux, workers are implemented using lightweight threads called fibers, and on other platforms workers are implemented using operating system threads. Workers execute tasks that are assigned to them by the database server kernel. The database server utilizes a variable-sized pool of workers to handle the server's workload. The size of the pool corresponds to the database server's multiprogramming level.

    Personal servers, as well as network servers on Windows Mobile, have a fixed-sized pool. For these servers, the number of workers created at server startup is controlled by the -gn option. For all other network servers, the database server creates the number of workers specified by the -gnh option. However, only the number of workers specified by the server multiprogramming level are allowed to service tasks. By default the size of the worker pool is dynamically tuned by the kernel in response to changes in the database server's workload, and can fluctuate between the lower and upper bounds specified by the -gnl and -gnh options respectively. See Database server configuration of the multiprogramming level.

    Tasks are assigned to workers on a first-in, first-out (FIFO) basis. Each task's priority is set based on the connection that generated that task. Once a task is assigned to a worker for execution, the kernel's scheduler takes the task's priority into account when allocating CPU time to that worker. During task execution, if a task needs to block for some reason during its processing, such as while waiting for a lock or for I/O to complete, the worker remains coupled to that task. When the task completes, only then does the worker become available to execute other tasks.

  • Thread   A thread, or thread of execution, is an operating system construct that permits concurrent execution within a single process. Every operating system process, including the database server, is executed by at least one, and possibly many threads. A thread is scheduled outside the application by the operating system, and ultimately, all of an application's execution is performed by its threads. On Windows and Linux, the database server creates a fixed number of threads: one operating system thread per CPU core, controlled by the -gtc option. On other platforms, the number of operating system threads created is equivalent to the size of the worker pool and is controlled using the same mechanisms that are used to control the worker pool size. See -gtc dbeng12/dbsrv12 server option and -gn dbsrv12 server option.

    The number of threads is independent of the number of connections to the database, and the database server does not dedicate a thread to a specific connection. Instead, as tasks enter the database server for execution, they are dynamically assigned a thread from a fixed-size pool of server threads. Once a task is assigned to a thread, the thread processes the task until the task completes or is canceled.

 See also

Workers on Unix
Workers on Windows and Linux
Threading behavior
Database server configuration of the multiprogramming level