Database Transactions

A transaction is a collection of database operations treated as a single logical operation.

For example, registering a student may require inserting information into more than one table.

Student Insert
       +
Fee Record Insert
       +
Course Record Insert

These operations can be handled as one transaction.

Transactions help prevent partially completed operations.

• Maintain data consistency
• Prevent incomplete database changes
• Allow multiple operations to succeed together
• Allow changes to be cancelled when an error occurs
• Improve reliability of APIs

Suppose a student registration API performs two operations:

INSERT student
INSERT student_course

If the first operation succeeds but the second operation fails, the database may contain incomplete information.

A transaction can prevent this situation.

A typical transaction follows this process:

1. Start transaction
2. Execute database operations
3. Check for errors
4. Commit if everything succeeds
5. Rollback if something fails

BEGIN
   ↓
Database Operations
   ↓
Success?
 ↙     ↘
Yes     No
 ↓       ↓
COMMIT  ROLLBACK

A transaction is started before performing the related database operations.

With PDO:

$pdo->beginTransaction();

After this point, changes can be committed or rolled back.

COMMIT permanently saves all changes made during the transaction.

$pdo->commit();

Usually, commit should only be called after all required operations complete successfully.

ROLLBACK cancels the changes made during the current transaction.

$pdo->rollBack();

It is normally used when an operation fails.

try {

    $pdo->beginTransaction();

    $pdo->exec(
        "INSERT INTO students (name)
         VALUES ('Rahul')"
    );

    $pdo->exec(
        "INSERT INTO student_courses (student_id, course)
         VALUES (1, 'React Native')"
    );

    $pdo->commit();

} catch (Exception $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    echo "Transaction failed";
}

Prepared statements should still be used inside transactions.

try {

    $pdo->beginTransaction();

    $stmt = $pdo->prepare(
        "INSERT INTO students (name, email)
         VALUES (?, ?)"
    );

    $stmt->execute([
        "Rahul",
        "rahul@example.com"
    ]);

    $pdo->commit();

} catch (Exception $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
}

Transactions are useful when several INSERT operations depend on each other.

$pdo->beginTransaction();

$studentStmt = $pdo->prepare(
    "INSERT INTO students (name, email)
     VALUES (?, ?)"
);

$studentStmt->execute([
    $name,
    $email
]);

$studentId = $pdo->lastInsertId();

$courseStmt = $pdo->prepare(
    "INSERT INTO student_courses
     (student_id, course)
     VALUES (?, ?)"
);

$courseStmt->execute([
    $studentId,
    $course
]);

$pdo->commit();

A transaction can also protect multiple related UPDATE operations.

$pdo->beginTransaction();

$stmt1 = $pdo->prepare(
    "UPDATE students
     SET status = ?
     WHERE id = ?"
);

$stmt1->execute([
    "active",
    $studentId
]);

$stmt2 = $pdo->prepare(
    "UPDATE student_courses
     SET status = ?
     WHERE student_id = ?"
);

$stmt2->execute([
    "active",
    $studentId
]);

$pdo->commit();

Transactions can also be useful when deleting related records.

$pdo->beginTransaction();

$stmt1 = $pdo->prepare(
    "DELETE FROM student_courses
     WHERE student_id = ?"
);

$stmt1->execute([$studentId]);

$stmt2 = $pdo->prepare(
    "DELETE FROM students
     WHERE id = ?"
);

$stmt2->execute([$studentId]);

$pdo->commit();

If an error occurs before commit, the API can roll back the transaction.

try {

    $pdo->beginTransaction();

    // Operation 1
    // Operation 2
    // Operation 3

    $pdo->commit();

} catch (Exception $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

}

The database is returned to the state before the transaction began.

A REST API can use a transaction when one request needs multiple database operations.

POST /api/register.php

Request
   ↓
Validate Data
   ↓
Start Transaction
   ↓
Insert User
   ↓
Insert Profile
   ↓
Commit
   ↓
JSON Response

If the transaction fails, the API should return a safe JSON error.

http_response_code(500);

echo json_encode([
    "success" => false,
    "message" => "Unable to complete the operation",
    "data" => null
]);

Do not expose database passwords, SQL statements, or sensitive exception details to the mobile application.

Validation should normally happen before starting the database transaction when possible.

if (empty($name) || empty($email)) {

    http_response_code(422);

    echo json_encode([
        "success" => false,
        "message" => "Validation failed"
    ]);

    exit;
}

$pdo->beginTransaction();

Before calling rollback inside an error handler, you can check whether a transaction is currently active.

if ($pdo->inTransaction()) {
    $pdo->rollBack();
}

This prevents attempting a rollback when no transaction is active.

Database transactions are commonly combined with exception handling.

try {

    $pdo->beginTransaction();

    // Database operations

    $pdo->commit();

} catch (PDOException $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    error_log($e->getMessage());
}

Log technical details on the server while returning a safe message to the client.

Consider a student registration API that creates a student and payment record.

$pdo->beginTransaction();

$studentStmt = $pdo->prepare(
    "INSERT INTO students (name, email)
     VALUES (?, ?)"
);

$studentStmt->execute([
    $name,
    $email
]);

$studentId = $pdo->lastInsertId();

$paymentStmt = $pdo->prepare(
    "INSERT INTO payments (student_id, amount)
     VALUES (?, ?)"
);

$paymentStmt->execute([
    $studentId,
    $amount
]);

$pdo->commit();

If the payment record cannot be inserted, the registration can be rolled back.

An atomic operation is treated as one complete unit.

Either all required changes are successfully saved, or the transaction can roll them back.

All Operations
      ↓
Successful
      ↓
   COMMIT

If Failure
      ↓
 ROLLBACK

Data consistency means related database records should remain logically correct after an operation.

For example, if a student is created but the required course record is not, the application may contain inconsistent data.

Transactions help avoid this type of partial operation.

The transaction itself happens on the PHP/MySQL server. React Native sends the API request and receives the final result.

const response = await fetch(API_URL, {
    method: "POST",
    headers: {
        "Content-Type": "application/json"
    },
    body: JSON.stringify({
        name: "Rahul",
        email: "rahul@example.com"
    })
});

const result = await response.json();

if (result.success) {
    console.log("Registration successful");
} else {
    console.log(result.message);
}

Axios also communicates with the transaction-based API normally.

try {

    const response = await axios.post(
        API_URL,
        {
            name: "Rahul",
            email: "rahul@example.com"
        }
    );

    if (response.data.success) {
        console.log("Success");
    }

} catch (error) {

    console.log("Request failed");

}

React Native does not need to manage the database transaction itself.

Transactions require a database/storage engine that supports transactional behavior. In MySQL, InnoDB is commonly used for transactional tables.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
) ENGINE=InnoDB;

Commit should normally happen after all required operations have succeeded.

Incorrect approach:

$pdo->beginTransaction();

$pdo->commit();

// Another operation
// This operation is no longer
// protected by the previous transaction.

Plan the transaction boundary around the complete logical operation.

Transactions should contain the database work that needs to succeed or fail together.

• Validate input before the transaction when possible.
• Perform related database operations.
• Commit after success.
• Rollback after failure.
• Avoid unnecessary long-running work inside a transaction.

try {

    $pdo->beginTransaction();

    $stmt = $pdo->prepare(
        "INSERT INTO students (name, email)
         VALUES (?, ?)"
    );

    $stmt->execute([
        $name,
        $email
    ]);

    $studentId = $pdo->lastInsertId();

    $payment = $pdo->prepare(
        "INSERT INTO payments
         (student_id, amount)
         VALUES (?, ?)"
    );

    $payment->execute([
        $studentId,
        $amount
    ]);

    $pdo->commit();

    http_response_code(201);

    echo json_encode([
        "success" => true,
        "message" => "Student registered successfully",
        "data" => [
            "student_id" => $studentId
        ]
    ]);

} catch (PDOException $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    error_log($e->getMessage());

    http_response_code(500);

    echo json_encode([
        "success" => false,
        "message" => "Unable to complete registration",
        "data" => null
    ]);
}

• Forgetting to call commit()
• Forgetting to call rollBack() after failure
• Starting a transaction too late
• Committing before all operations finish
• Not using prepared statements
• Returning sensitive database errors
• Keeping transactions open unnecessarily long
• Using non-transactional database tables

• Use transactions for related database operations.
• Validate input before starting when possible.
• Use PDO prepared statements.
• Start with beginTransaction().
• Call commit() only after successful operations.
• Call rollBack() when an operation fails.
• Use inTransaction() before rollback in error handling.
• Log technical errors securely on the server.
• Return safe JSON responses to React Native.
• Keep transactions short and focused.