Transactions with MariaDB Connector/C

MariaDB Connector/C supports multi-statement transactions with manual commit and rollback by disabling auto-commit with mysql_autocommit() on the connection.

Developers can use MariaDB Connector/C to perform basic DML (Data Manipulation Language) operations inside a transaction with MariaDB database products.

Auto-Commit Behavior

By default, MariaDB Connector/C enables auto-commit. When auto-commit is enabled, each SQL statement is executed in its own transaction.

Multi-Statement Transactions

MariaDB Connector/C supports multi-statement transactions when auto-commit is disabled.

Disable auto-commit by calling mysql_autocommit() with a mode of 0:

mysql_autocommit(conn, 0);

When auto-commit is disabled, a new transaction is automatically started when the current transaction is manually committed or rolled back. The application does not need to manually start each new transaction with START TRANSACTION or BEGIN.

The transaction can be manually managed by performing the following operations:

• Commit the transaction by calling mysql_commit() or using COMMIT.

• Roll back the transaction by calling mysql_rollback() or using ROLLBACK.

• Re-enable auto-commit by calling mysql_autocommit() with a mode of 1.

Code Example: DML in a Transaction

UPDATE, INSERT, and DELETE are DML operations that modify data in a table.

The following code demonstrates how to execute several UPDATE statements on the example table within a single transaction with auto-commit disabled. All of the updates are committed together; if any update fails, the transaction is rolled back so none of them are applied.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

// Update a contact's email by first name using the prepared statement
static void update_contact(MYSQL_STMT *stmt, const char *email, const char *first_name)
{
   MYSQL_BIND bind[2];
   memset(bind, 0, sizeof(bind));
   bind[0].buffer_type = MYSQL_TYPE_STRING;
   bind[0].buffer = (char *) email;
   bind[0].buffer_length = strlen(email);
   bind[1].buffer_type = MYSQL_TYPE_STRING;
   bind[1].buffer = (char *) first_name;
   bind[1].buffer_length = strlen(first_name);

   if (mysql_stmt_bind_param(stmt, bind) || mysql_stmt_execute(stmt))
      fprintf(stderr, "Error updating contact: %s\n", mysql_stmt_error(stmt));
}

int main(int argc, char *argv[])
{
   // Initialize Connection
   MYSQL *conn;
   if (!(conn = mysql_init(0)))
   {
      fprintf(stderr, "unable to initialize connection struct\n");
      exit(1);
   }

   // Connect to the database
   if (!mysql_real_connect(
         conn, "mariadb.example.net", "db_user", "db_user_password",
         "test", 3306, NULL, 0))
   {
      fprintf(stderr, "Error connecting to Server: %s\n", mysql_error(conn));
      mysql_close(conn);
      exit(1);
   }

   // Disable auto-commit to start a user-managed transaction
   mysql_autocommit(conn, 0);

   // Prepare the UPDATE statement once and reuse it
   MYSQL_STMT *stmt = mysql_stmt_init(conn);
   if (mysql_stmt_prepare(stmt,
         "UPDATE test.contacts SET email=? WHERE first_name=?", -1))
   {
      fprintf(stderr, "Error preparing statement: %s\n", mysql_stmt_error(stmt));
      exit(1);
   }

   // Apply several updates within the transaction
   update_contact(stmt, "[e-mail]",   "John");
   update_contact(stmt, "[e-mail]",    "Jon");
   update_contact(stmt, "[e-mail]", "Johnny");

   // Commit the transaction; roll back on failure
   if (mysql_commit(conn))
   {
      fprintf(stderr, "Error committing transaction: %s\n", mysql_error(conn));
      mysql_rollback(conn);
   }

   // Close the statement and the connection
   mysql_stmt_close(stmt);
   mysql_close(conn);

   return 0;
}

The query below confirms the UPDATE of the example table:

SELECT * FROM test.contacts;

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  1 | John       | Smith     | [e-mail]   |
|  2 | Jon        | Smith     | [e-mail]    |
|  3 | Johnny     | Smith     | [e-mail] |
+----+------------+-----------+-------------------------+