Antonio SilvaWhat does a database transaction mean?
A database transaction is a sequence of multiple operations performed on a database and all of which served as a single logical unit of work either occurring fully or not at all. In other words, there is never a case of only half of the operations being performed and the results saved. When a database transaction is in progress, the state of the database may be temporarily inconsistent, but when the transaction commits or ends, the changes are applied.
During its lifecycle, a database transaction goes through multiple states. These states are called transaction states and are typically one of the following:
- Active states: It is the first state during the execution of a transaction. A transaction is active as long as its instructions (read or write operations) are performed.
- Partially committed: A change has been executed in this state, but the database has not yet committed the change on disk. In this state, data is stored in the memory buffer, and the buffer is not yet written to disk.
- Committed: In this state, all the transaction updates are permanently stored in the database. Therefore, it is not possible to rollback the transaction after this point.
- Failed: If a transaction fails or has been aborted in the active state or partially committed state, it enters into a failed state.
- Terminated state: This is the last and final transaction state after a committed or aborted state. This marks the end of the database transaction life cycle.
What are ACID and why are they important?
ACID is an acronym that stands for Atomicity, Consistency, Isolation, Durability:
Atomicity: Everything is accomplished or nothing;
Consistency: DB is intact after the transaction;
Isolation: Result visible only after the transaction is successful;
Durability: Persistence when successful, not lost in case of failure.
ACID is especially concerned with how a database recovers from any failure that might occur while processing a transaction.
An ACID-compliant DBMS ensures that the data in the database remains accurate and consistent despite any such failure.
Example
Step 1 - Directory System:
📦ClassTransaction
┣ 📂api
┃ ┣ 📜Connection.php
┃ ┗ 📜Transaction.php
┣ 📂classes
┃ ┗ 📜Product.php
┣ 📂config
┃ ┗ 📜config.ini
┣ 📂database
┃ ┗ 📜product.db
┗ 📜index.php
Step 2 - ini configuration file:
host =
name = database/product.db
user =
pass =
type = sqlite
port =
Step 3 - DataBase:
CREATE TABLE product(
id INTEGER PRIMARY KEY NOT NULL,
description TEXT,
stock FLOAT,
cost_price FLOAT,
sale_price FLOAT,
bar_code TEXT,
date_register DATE,
origin CHAR(1)
);
Step 4 - Connection Class:
You can find the implementation Connection class here, or you can download it here.
Step 5 - Transaction Class:
<?php
namespace api;
class Transaction
{
// Property for storing connection status
private static $conn;
private function __construct()
{
}
}
- open method
// Open Transaction
public static function open(string $database): void
{
// Open Connection with DataBase
self::$conn = Connection::open($database);
/**
* Start a transaction in PDO
* The beginTransaction() method turns off
* the autocommit mode
*/
self::$conn->beginTransaction();
}
- close method
// Close Transaction
public static function close(): void
{
// If there is a connection
if (self::$conn) {
// commit a transaction
self::$conn->commit();
// Clear the connection
self::$conn = null;
}
}
- rollback method
// Rollback Transaction
public static function rollback(): void
{
// If there is a connection
if (self::$conn) {
/**
* The PDO::rollback() method rolls back all
* changes made to the database.
* Also, it returns the connection to the autocommit mode.
*/
self::$conn->rollback();
// Clear the connection
self::$conn = null;
}
}
- get method
// Take the Connection
public static function get(): object
{
// Return the Connection
return self::$conn;
}
Step 6 - Product Class:
Download the Product class here.
Here you find the implementation.
Testing
<?php
require_once 'api/Connection.php';
require_once 'api/Transaction.php';
require_once 'classes/Product.php';
use api\Connection;
use api\Transaction;
use classes\Product;
try {
/**
* open Transaction
* Passive the name of the connection
* file with the database.
*/
Transaction::open('config');
// Statment
// close Transaction
Transaction::close();
} catch (Exception $e) {
// Undo the command package if there is any error
Transaction::rollback();
echo $e->getMessage();
}
- INSERT
$product = new Product();
$product->description = 'Chocolate';
$product->stock = 80;
$product->cost_price = 4;
$product->sale_price = 7;
$product->bar_code = '3476394';
$product->date_register = date('Y-m-d');
$product->origin = 'N';
$product->save();
$produto2 = new Product();
$produto2->description = 'Orange';
$produto2->stock = 5;
$produto2->cost_price = 100;
$produto2->sale_price = 5;
$produto2->bar_code = '2431313';
$produto2->date_register = date('Y-m-d');
$produto2->origin = 'L';
$produto2->save();
- ROLLBACK
$product = new Product();
$product->description = 'Apple';
$product->stock = 50;
$product->cost_price = 6;
$product->sale_price = 10;
$product->bar_code = '7796394';
$product->date_register = date('Y-m-d');
$product->origin = 'A';
$product->save();
$produto2 = new Product();
$produto2->description = 'Banana';
$produto2->stock = 20;
$produto2->cost_price = 12;
// ERROR
$produto2->sale_price = ;
$produto2->bar_code = '4561313';
$produto2->date_register = date('Y-m-d');
$produto2->origin = 'B';
$produto2->save();
Nothing of the data package was saved in the database by presenting an error.
