Documentation Home
X DevAPI User Guide
Download this Manual

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

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.

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

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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.