All pages
Powered by GitBook
1 of 4

Non-Blocking Client Library

MariaDB client library (starting with version 5.5.21) and MySQL Connector/C (starting with version 2.1.0) supports _non-blocking_ operations

About Non-blocking Operation in the Client Library

MariaDB, starting with version 5.5.21 supports non-blocking operations in the client-library. This allows an application to start a query or other operation against the database, and then continue to do other work (in the same thread) while the request is sent over the network, the query is processed in the server, and the result travels back. As parts of the result become ready, the application can — at its leisure — call back into the library to continue processing, repeating this until the operation is completed.

Non-blocking operation is implemented entirely within the client library. This means no special server support is necessary and non-blocking operation works with any version of the MariaDB or MySQL server, the same as the normal blocking API. It also means that it is not possible to have two queries running at the same time on the same connection (this is a protocol limitation). But a single thread can have any number of non-blocking queries running at the same time, each using its own MYSQL connection object.

Non-blocking operation is useful when an application needs to run a number of independent queries in parallel at the same time, to speed up operation compared to running them sequentially one after the other. This could be multiple queries against a single server (to better utilize multiple CPU cores and/or a high-capacity I/O system on the server), or it could be queries against multiple servers (e.g. SHOW STATUS against all running servers for monitoring, or a map/reduce-like operation against a big sharded database).

Non-blocking operation is also very useful in applications that are already written in a non-blocking style, for example using a framework likelibevent, or, for example, a GUI-application using an event loop. Using the non-blocking client library allows the integrations of database queries into such applications, without the risk of long-running queries "hanging" the user interface or stalling the event loop, and without having to manually spawn separate threads to run the queries and re-synchronize with the threads to get the results back.

In this context, "blocking" means the situation where communication on the network socket to the server has to wait while processing the query. Waiting can be necessary because the server has not yet had time to process the query, or because the data needs to travel over the network from the server, or even because the first part of a large request needs to be sent out on the network before local socket buffers can accept the last part. Whenever such a wait is necessary, control returns to the application. The application will then runselect() or poll() (or something similar) to detect when any wait condition is satisfied, and then call back into the library to continue processing.

An example program is available in the MariaDB source tree:

tests/async_queries.c

It uses libevent to run a set of queries in parallel from within a single thread / event loop. This is a good example of how to integrate non-blocking query processing into an event-based framework.

The non-blocking API in the client library is entirely optional. The new library is completely ABI- and source-compatible with existing applications. Also, applications not using non-blocking operations are not affected, nor is there any significant performance penalty for having support for non-blocking operations in the library for applications which do not use them.

The library internally uses co-routines, and requires a co-routine implementation to work. Native implementations are included for i386, amd64, and (since Connector/C version 3.3.12) aarch64 architectures. For other architectures, a fallback to ucontext is automatically used if available. An alternate fallback boost::context can also be used instead of ucontext by building with -DWITH_BOOST_CONTEXT=ON (boost::context is not used by default). If no co-routine implementation is available the non-blocking operations are disabled and will not work.

This page is licensed: CC BY-SA / Gnu FDL

Non-blocking API Reference

Here is a list of all functions in the non-blocking client API and their parameters. Apart from operating in a non-blocking way, they all work exactly the same as their blocking counterparts, so their exact semantics can be obtained from the documentation of the normal client API.

The API also contains the following three functions which are used to get the socket fd and timeout values when waiting for events to occur:

my_socket mysql_get_socket(const MYSQL *mysql)

Return the descriptor of the socket used for the connection.

unsigned int STDCALL mysql_get_timeout_value(const MYSQL *mysql)

This should only be called when a _start() or _cont() function returns a value with the MYSQL_WAIT_TIMEOUT flag set. In this case, it returns the value, in seconds, after which a timeout has occured and the application should call the appropriate _cont() function passingMYSQL_WAIT_TIMEOUT as the event that occured.

This is used to handle connection and read timeouts.

unsigned int STDCALL mysql_get_timeout_value_ms(const MYSQL *mysql)

This function is available starting from MariaDB 5.5.28 and MariaDB 10.0.0.

Like mysql_get_timeout_value(), this should only be called when a _start() or _cont() function returns a value with the MYSQL_WAIT_TIMEOUT flag set. In this case, it returns the value, in millisecondsseconds, after which a timeout has occured and the application should call the appropriate _cont() function passing MYSQL_WAIT_TIMEOUT as the event that occured.

The difference to mysql_get_timeout_value() is that this provides millisecond resolution for timeouts, rather than just whole seconds. In MariaDB 10.0, internal timeouts can now be in milliseconds, while in 5.5 and below it was only whole seconds.

This milliseconds version is provided also in MariaDB 5.5 (from 5.5.28 onwards) to make it easier for applications to work with either library version. However, in 5.5 it always returns a multiple of 1000 milliseconds.

At the end is a list of all functions from the normal API which can be used safely in a non-blocking program, since they never need to block.

int mysql_real_connect_start(MYSQL **ret, MYSQL *mysql, const char *host,
                         const char *user, const char *passwd, const char *db,
                         unsigned int port, const char *unix_socket,
                         unsigned long client_flags)
int mysql_real_connect_cont(MYSQL **ret, MYSQL *mysql, int ready_status)

mysql_real_connect_start() initiates a non-blocking connection request to a server.

When mysql_real_connect_start() or mysql_real_connect_cont() returns zero, a copy of the passed 'mysql' argument is stored in *ret.

int mysql_real_query_start(int *ret, MYSQL *mysql, const char *stmt_str,
unsigned long length)
int mysql_real_query_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_fetch_row_start(MYSQL_ROW *ret, MYSQL_RES *result)
int mysql_fetch_row_cont(MYSQL_ROW *ret, MYSQL_RES *result, int ready_status)

Initiate fetch of another row from a SELECT query.

If the MYSQL_RES was obtained from mysql_use_result(), then this function allows stream processing, where initial rows are returned to the application while the server is still sending subsequent rows. When no more data is available on the socket, mysql_fetch_row_start() ormysql_fetch_row_cont() will return MYSQL_WAIT_READ (or possiblyMYSQL_WAIT_WRITE if using TLS and TLS re-negotiation is needed; alsoMYSQL_WAIT_TIMEOUT may be set if read timeout is enabled). When data becomes available, more rows can be fetched with mysql_fetch_row_cont().

If the MYSQL_RES was obtained from mysql_store_result() /mysql_store_result_start() / mysql_store_result_cont(), then this function cannot block — mysql_fetch_row_start() will always return 0 (and if desired, plain mysql_fetch_row() may be used instead with equivalent effect).

int mysql_set_character_set_start(int *ret, MYSQL *mysql, const char *csname)
int mysql_set_character_set_cont(int *ret, MYSQL *mysql, int ready_status)
mysql_select_db_start(int *ret, MYSQL *mysql, const char *db)
int mysql_select_db_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_send_query_start(int *ret, MYSQL *mysql, const char *q, unsigned long length)
int mysql_send_query_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_store_result_start(MYSQL_RES **ret, MYSQL *mysql)
int mysql_store_result_cont(MYSQL_RES **ret, MYSQL *mysql, int ready_status)
int mysql_free_result_start(MYSQL_RES *result)
int mysql_free_result_cont(MYSQL_RES *result, int ready_status)

This function can need to wait if not all rows were fetched before it was called (then it needs to consume any pending rows sent from the server so they do not interfere with any subsequent queries sent).

If all rows were already fetched, then this function will not need to wait.mysql_free_result_start() will return zero (or if so desired, plainmysql_free_result() may be used instead).

Note that mysql_free_result() returns no value, so there is no extra 'ret' parameter for mysql_free_result_start() ormysql_free_result_cont().

int mysql_close_start(MYSQL *sock)
int mysql_close_cont(MYSQL *sock, int ready_status)

mysql_close() sends a COM_QUIT request to the server, though it does not wait for any reply.

Thus teoretically it can block (if the socket buffer is full), though in practise it is probably unlikely to occur frequently.

The non-blocking version of mysql_close() is provided for completeness; for many applications using the normal mysql_close() is probably sufficient (and may be simpler).

Note that mysql_close() returns no value, so there is no extra 'ret' parameter for mysql_close_start() or mysql_close_cont().

int mysql_change_user_start(my_bool *ret, MYSQL *mysql, const char *user, const
                            char *passwd, const char *db)
int mysql_change_user_cont(my_bool *ret, MYSQL *mysql, int ready_status)
int mysql_query_start(int *ret, MYSQL *mysql, const char *q)
int mysql_query_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_shutdown_start(int *ret, MYSQL *mysql, enum mysql_enum_shutdown_level
                        shutdown_level)
int mysql_shutdown_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_dump_debug_info_start(int *ret, MYSQL *mysql)
int mysql_dump_debug_info_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_refresh_start(int *ret, MYSQL *mysql, unsigned int refresh_options)
int mysql_refresh_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_kill_start(int *ret, MYSQL *mysql, unsigned long pid)
int mysql_kill_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_set_server_option_start(int *ret, MYSQL *mysql,
                              enum enum_mysql_set_option option)
int mysql_set_server_option_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_ping_start(int *ret, MYSQL *mysql)
int mysql_ping_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_stat_start(const char **ret, MYSQL *mysql)
int mysql_stat_cont(const char **ret, MYSQL *mysql, int ready_status)
int mysql_list_dbs_start(MYSQL_RES **ret, MYSQL *mysql, const char *wild)
int mysql_list_dbs_cont(MYSQL_RES **ret, MYSQL *mysql, int ready_status)
int mysql_list_tables_start(MYSQL_RES **ret, MYSQL *mysql, const char *wild)
int mysql_list_tables_cont(MYSQL_RES **ret, MYSQL *mysql, int ready_status)
int mysql_list_processes_start(MYSQL_RES **ret, MYSQL *mysql)
int mysql_list_processes_cont(MYSQL_RES **ret, MYSQL *mysql, int ready_status)
int mysql_list_fields_start(MYSQL_RES **ret, MYSQL *mysql, const char *table,
                        const char *wild)
int mysql_list_fields_cont(MYSQL_RES **ret, MYSQL *mysql, int ready_status)
int mysql_read_query_result_start(my_bool *ret, MYSQL *mysql)
int mysql_read_query_result_cont(my_bool *ret, MYSQL *mysql, int ready_status)
int mysql_stmt_prepare_start(int *ret, MYSQL_STMT *stmt, const char *query,
                         unsigned long length)
int mysql_stmt_prepare_cont(int *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_execute_start(int *ret, MYSQL_STMT *stmt)
int mysql_stmt_execute_cont(int *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_fetch_start(int *ret, MYSQL_STMT *stmt)
int mysql_stmt_fetch_cont(int *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_store_result_start(int *ret, MYSQL_STMT *stmt)
int mysql_stmt_store_result_cont(int *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_close_start(my_bool *ret, MYSQL_STMT *stmt)
int mysql_stmt_close_cont(my_bool *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_reset_start(my_bool *ret, MYSQL_STMT *stmt)
int mysql_stmt_reset_cont(my_bool *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_free_result_start(my_bool *ret, MYSQL_STMT *stmt)
int mysql_stmt_free_result_cont(my_bool *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_stmt_send_long_data_start(my_bool *ret, MYSQL_STMT *stmt,
                                unsigned int param_number,
                                const char *data, unsigned long length)
int mysql_stmt_send_long_data_cont(my_bool *ret, MYSQL_STMT *stmt, int ready_status)
int mysql_commit_start(my_bool *ret, MYSQL *mysql)
int mysql_commit_cont(my_bool *ret, MYSQL *mysql, int ready_status)
int mysql_rollback_start(my_bool *ret, MYSQL *mysql)
int mysql_rollback_cont(my_bool *ret, MYSQL *mysql, int ready_status)
int mysql_autocommit_start(my_bool *ret, MYSQL *mysql, my_bool auto_mode)
int mysql_autocommit_cont(my_bool *ret, MYSQL *mysql, int ready_status)
int mysql_next_result_start(int *ret, MYSQL *mysql)
int mysql_next_result_cont(int *ret, MYSQL *mysql, int ready_status)
int mysql_stmt_next_result_start(int *ret, MYSQL_STMT *stmt)
int mysql_stmt_next_result_cont(int *ret, MYSQL_STMT *stmt, int ready_status)

Client API functions which never block

The following client API functions never need to do I/O and thus can never block. Therefore, they can be used as normal in programs using non-blocking operations; no need to call any special _start() variant. (Even if a_start() variant was available, it would always return zero, so no_cont() call would ever be needed).

This page is licensed: CC BY-SA / Gnu FDL

Using the Non-blocking Library

The MariaDB non-blocking client API is modelled after the normal blocking library calls. This makes it easy to learn and remember. It makes it easier to translate code from using the blocking API to using the non-blocking API (or vice versa). And it also makes it simple to mix blocking and non-blocking calls in the same code path.

For every library call that may block on socket I/O, such as 'int mysql_real_query(MYSQL, query, query_length)', two additional non-blocking calls are introduced:

int mysql_real_query_start(&status, MYSQL, query, query_length)
int mysql_real_query_cont(&status, MYSQL, wait_status)

To do non-blocking operation, an application first callsmysql_real_query_start() instead of mysql_real_query(), passing the same parameters.

If mysql_real_query_start() returns zero, then the operation completed without blocking, and 'status' is set to the value that would normally be returned from mysql_real_query().

Else, the return value from mysql_real_query_start() is a bitmask of events that the library is waiting on. This can be MYSQL_WAIT_READ,MYSQL_WAIT_WRITE, or MYSQL_WAIT_EXCEPT, corresponding to the similar flags for select() or poll(); and it can include MYSQL_WAIT_TIMEOUT when waiting for a timeout to occur (e.g. a connection timeout).

In this case, the application continues other processing and eventually checks for the appropriate condition(s) to occur on the socket (or for timeout). When this occurs, the application can resume the operation by callingmysql_real_query_cont(), passing in 'wait_status' a bitmask of the events which actually occurred.

Just like mysql_real_query_start(), mysql_real_query_cont() returns zero when done, or a bitmask of events it needs to wait on. Thus the application continues to repeatedly call mysql_real_query_cont(), intermixed with other processing of its choice; until zero is returned, after which the result of the operation is stored in 'status'.

Some calls, like mysql_option(), do not do any socket I/O, and so can never block. For these, there are no separate _start() or _cont() calls. See the "Non-blocking API reference" page for a full list of what functions can and can not block.

The checking for events on the socket / timeout can be done with select() or poll() or a similar mechanism. Though often it will be done using a higher-level framework (such as libevent), which supplies facilities for registering and acting on such conditions.

The descriptor of the socket on which to check for events can be obtained by calling mysql_get_socket(). The duration of any timeout can be obtained from mysql_get_timeout_value().

Here is a trivial (but full) example of running a query with the non-blocking API. The example is found in the MariaDB source tree asclient/async_example.c. (A larger, more realistic example using libevent is found as tests/async_queries.c in the source):

static void run_query(const char *host, const char *user, const char *password) {
  int err, status;
  MYSQL mysql, *ret;
  MYSQL_RES *res;
  MYSQL_ROW row;

  mysql_init(&mysql);
  mysql_options(&mysql, MYSQL_OPT_NONBLOCK, 0);

  status = mysql_real_connect_start(&ret, &mysql, host, user, password, NULL, 0, NULL, 0);
  while (status) {
    status = wait_for_mysql(&mysql, status);
    status = mysql_real_connect_cont(&ret, &mysql, status);
  }

  if (!ret)
    fatal(&mysql, "Failed to mysql_real_connect()");

  status = mysql_real_query_start(&err, &mysql, SL("SHOW STATUS"));
  while (status) {
    status = wait_for_mysql(&mysql, status);
    status = mysql_real_query_cont(&err, &mysql, status);
  }
  if (err)
    fatal(&mysql, "mysql_real_query() returns error");

  /* This method cannot block. */
  res= mysql_use_result(&mysql);
  if (!res)
    fatal(&mysql, "mysql_use_result() returns error");

  for (;;) {
    status= mysql_fetch_row_start(&row, res);
    while (status) {
      status= wait_for_mysql(&mysql, status);
      status= mysql_fetch_row_cont(&row, res, status);
    }
    if (!row)
      break;
    printf("%s: %s\n", row[0], row[1]);
  }
  if (mysql_errno(&mysql))
    fatal(&mysql, "Got error while retrieving rows");
  mysql_free_result(res);
  mysql_close(&mysql);
}

/* Helper function to do the waiting for events on the socket. */
static int wait_for_mysql(MYSQL *mysql, int status) {
  struct pollfd pfd;
  int timeout, res;

  pfd.fd = mysql_get_socket(mysql);
  pfd.events =
    (status & MYSQL_WAIT_READ ? POLLIN : 0) |
    (status & MYSQL_WAIT_WRITE ? POLLOUT : 0) |
    (status & MYSQL_WAIT_EXCEPT ? POLLPRI : 0);
  if (status & MYSQL_WAIT_TIMEOUT)
    timeout = 1000*mysql_get_timeout_value(mysql);
  else
    timeout = -1;
  res = poll(&pfd, 1, timeout);
  if (res == 0)
    return MYSQL_WAIT_TIMEOUT;
  else if (res < 0)
    return MYSQL_WAIT_TIMEOUT;
  else {
    int status = 0;
    if (pfd.revents & POLLIN) status |= MYSQL_WAIT_READ;
    if (pfd.revents & POLLOUT) status |= MYSQL_WAIT_WRITE;
    if (pfd.revents & POLLPRI) status |= MYSQL_WAIT_EXCEPT;
    return status;
  }
}

Setting MYSQL_OPT_NONBLOCK

Before using any non-blocking operation, it is necessary to enable it first by setting the MYSQL_OPT_NONBLOCK option:

mysql_options(&mysql, MYSQL_OPT_NONBLOCK, 0);

This call can be made at any time — typically it will be done at the start, before mysql_real_connect(), but it can be done at any time to start using non-blocking operations.

If a non-blocking operation is attempted without setting theMYSQL_OPT_NONBLOCK option, the program will typically crash with a NULL pointer exception.

The argument for MYSQL_OPT_NONBLOCK is the size of the stack used to save the state of a non-blocking operation while it is waiting for I/O and the application is doing other processing. Normally, applications will not have to change this, and it can be passed as zero to use the default value.

Mixing blocking and non-blocking operation

It is possible to freely mix blocking and non-blocking calls on the sameMYSQL connection.

Thus, an application can do a normal blocking mysql_real_connect() and subsequently do a non-blocking mysql_real_query_start(). Or vice versa, do a non-blocking mysql_real_connect_start(), and later do a blockingmysql_real_query() on the resulting connection.

Mixing can be useful to allow code to use the simpler blocking API in parts of the program where waiting is not a problem. For example establishing the connection(s) at program startup, or doing small quick queries between large, long-running ones.

The only restriction is that any previous non-blocking operation must have finished before starting a new blocking (or non-blocking) operation, see the next section: "Terminating a non-blocking operation early" below.

Terminating a non-blocking operation early

When a non-blocking operation is started with mysql_real_query_start() or another _start() function, it must be allowed to finish before starting a new operation. Thus, the application must continue calling mysql_real_query_cont() until zero is returned, indicating that the operation is completed. It is not allowed to leave one operation "hanging" in the middle of processing and then start a new one on top of it.

It is, however, permissible to terminate the connection completely withmysql_close() in the middle of processing a non-blocking call. A new connection must then be initiated with mysql_real_connect before new queries can be run, either with a new MYSQL object or re-using the old one.

In the future, we may implement an abort facility to force an on-going operation to terminate as quickly as possible (but it will still be necessary to call mysql_real_query_cont() one last time after abort, allowing it to clean up the operation and return immediately with an appropriate error code).

Restrictions

DNS

When mysql_real_connect_start() is passed a hostname (as opposed to a local unix socket or an IP address, it may need to look up the hostname in DNS, depending on local host configuration (e.g. if the name is not in/etc/hosts or cached). Such DNS lookups do not happen in a non-blocking way. This means that mysql_real_connect_start() will not return control to the application while waiting for the DNS response. Thus the application may "hang" for some time if DNS is slow or non-functional.

If this is a problem, the application can pass an IP address tomysql_real_connect_start() instead of a hostname, which avoids the problem. The IP address can be obtained by the application with whatever non-blocking DNS loopup operation is available to it from the operating system or event framework used. Alternatively, a simple solution may be to just add the hostname to the local host lookup file (/etc/hosts on Posix/Unix/Linux machines).

Windows Named Pipes and Shared Memory connections

There is no support in the non-blocking API for connections using Windows named pipes or shared memory

Named pipes and shared memory can still be used, using either the blocking or the non-blocking API. However, operations that need to wait on I/O on the named pipe will not return control to the application; instead they will "hang" waiting for the operation to complete, just like the normal blocking API calls.

This page is licensed: CC BY-SA / Gnu FDL