3.3 Synchronous versus Asynchronous Execution

Traditionally, many MySQL drivers used a synchronous approach when executing SQL statements. This meant that operations such as opening connections and executing queries were blocked until completion, which could take a long time. To allow for parallel execution, a developer had to write a multithreaded application.

Any MySQL client that supports the X Protocol can provide asynchronous execution, either using callbacks, Promises, or by explicitly waiting on a specific result at the moment in time when it is actually needed.

Asynchronous Operations

Using callbacks is a very common way to implement asynchronous operations. When a callback function is specified, the CRUD operation is non-blocking which means that the next statement is called immediately even though the result from the database has not yet been fetched. Only when the result is available is the callback called.

Node.js JavaScript Code

var employees = db.getTable('employee');

employees.select('name', 'age')
  .where('name like :name')
  .orderBy('name')
  .bind('name', 'm%')
  .execute(function (row) {
    // do something with a row
  })
  .catch(err) {
    // Handle error
  });

C# Code

var employees = db.GetTable("employee");

var select = employees.Select("name", "age")
  .Where("name like :name")
  .OrderBy("name")
  .Bind("name", "m%")
  .ExecuteAsync();

select.ContinueWith(t =>
{
  if (t.Exception != null)
  {
    // Handle error
  }
  // Do something with the resultset
});

Java Code

Table employees = db.getTable("employee");

// execute the query asynchronously, obtain a future
CompletableFuture<RowResult> rowsFuture = employees.select("name", "age")
  .where("name like :name")
  .orderBy("name")
  .bind("name", "m%").executeAsync();

// dependent functions can be attached to the CompletableFuture

MySQL Shell JavaScript Code

// Asynchronous execution is not implemented

MySQL Shell Python Code

// Asynchronous execution is not implemented

Python Code

// Asynchronous execution is not implemented

C++ Code

// Asynchronous execution is not implemented

Asynchronous Operations using Awaits

Some languages can use an async/await pattern.

C# Code

Task<RowResult> getEmployeesTask = employees.Select("name", "age")
  .Where("name like :name").OrderBy("name")
  .Bind("name", "m%").ExecuteAsync();

// Do something else while the getEmployeesTask is executing in the background

// at this point we are ready to get our results back. If it is not done,
// this will block until done
RowResult res = await getEmployeesTask;

foreach (var row in res.FetchAll())
{
  // use row object
}

Connector/Node.js uses asynchronous operations using Promises for all network operations. See other examples.

Java Code

Table employees = db.getTable("employee");

// execute the query asynchronously, obtain a future
CompletableFuture<RowResult> rowsFuture = employees.select("name", "age")
  .where("name like :name")
  .orderBy("name")
  .bind("name", "m%").executeAsync();

// wait until it's ready
RowResult rows = rowsFuture.get();

Syntax Differences

Depending on which language you are using, the X DevAPI may implement a function such as executeAsync() in exchange for execute([mysqlx.Async]) or in addition to execute([mysqlx.Async]).

For example, in a Node.js context all executions are asynchronous. Therefore, Connector/Node.js does not need to distinguish between execute() and executeAsync(). To denote the asynchronous default execution, Connector/Node.js only implements execute() which returns JavaScript Promise objects.

Strongly typed programming languages, such as Java or C#, can take advantage of having two distinctly named API calls for synchronous and asynchronous executions. The two calls can have different return types. For example, Connector/J can use execute() to return a RowResult or DocResult and executeAsync() to return a CompletableFuture<T> where the type parameter is one of the result types.