Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  CRUD Operations  /  Synchronous versus Asynchronous Execution

Pre-General Availability Draft: 2017-05-19

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 multi-threaded 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.

Note

MySQL Shell does not support asynchronous operations.

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('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

C++ Code

// Asynchronous execution is not yet implemented in Connector/C++

Asynchronous Operations using Awaits

Languages such as C# 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();

C++ Code

// Asynchronous execution is not yet implemented in Connector/C++

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/Java 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.

Consult your language's Connector reference for more details, see Additional Documentation.


User Comments
Sign Up Login You must be logged in to post a comment.