Advanced PHP database connections: transactions, locks, and concurrency control

Advanced PHP database connections involve transactions, locks, and concurrency control to ensure data integrity and avoid errors. A transaction is an atomic unit of a set of operations, managed through the beginTransaction(), commit(), and rollback() methods. Locks prevent simultaneous access to data via PDO::LOCK_SHARED and PDO::LOCK_EXCLUSIVE. Concurrency control coordinates access to multiple transactions through MySQL isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). In practical applications, transaction, lock and concurrency control are used for product inventory management of shopping websites to ensure data integrity and avoid inventory problems.

Advanced PHP Database Connection: Transactions, Locks, and Concurrency Control

Understand advanced concepts such as transactions, locks, and concurrency control when using PHP for database interaction Crucial for guaranteeing data integrity and avoiding errors in applications.

Transaction

A transaction is a collection of database operations that are executed as an atomic unit. This means that all operations in the transaction either all succeed or all fail.

Use the beginTransaction(), commit() and rollback() methods to start, commit and rollback transactions:

$db->beginTransaction();
try {
    // 执行数据库操作
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}

Lock

Lock is used to prevent transactions from accessing the same database data at the same time. PHP's PDO provides two locking modes:

• PDO::LOCK_SHARED: allows transactions to read data, but not write.
• PDO::LOCK_EXCLUSIVE: Transactions are allowed to read and write data, but other transactions cannot access it at the same time.

To lock table rows through the query() method:

$stmt = $db->query('SELECT * FROM table WHERE id = 1 FOR UPDATE');

Concurrency control

The concurrency control mechanism ensures that when multiple transactions No data conflicts will occur when accessing the database simultaneously. MySQL provides the following isolation levels:

• READ UNCOMMITTED: Transactions can read uncommitted data from other transactions.
• READ COMMITTED: Transactions can only read data from other submitted transactions.
• Repeatable Read (REPEATABLE READ): A transaction can see the data of other committed transactions throughout its life cycle, but cannot see the data of uncommitted transactions.
• Serialization (SERIALIZABLE): Transactions are executed serially to avoid any concurrency conflicts.

You can set the isolation level through the PDO::ATTR_DEFAULT_FETCH_MODE option when connecting to the database:

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->setAttribute(PDO::ATTR_TRANSACTION_ISOLATION, PDO::ISOLATION_READ_COMMITTED);

Practical case

Consider a An online shopping website where users can add products to their shopping carts and make purchases. In order to prevent problems, such as the same product being added to the shopping cart by multiple users at the same time, transactions, locks, and concurrency control can be used to ensure data integrity:

// 启动事务
$db->beginTransaction();

// 锁定产品行
$stmt = $db->query('SELECT * FROM products WHERE id = :id FOR UPDATE');
$stmt->bindParam(':id', $productId);
$stmt->execute();

// 获取产品数量
$product = $stmt->fetch();
$quantity = $product['quantity'];

// 检查库存
if ($quantity <= 0) {
    $db->rollback();
    throw new RuntimeException('Product is out of stock.');
}

// 更新库存
$quantity--;
$stmt = $db->prepare('UPDATE products SET quantity = :quantity WHERE id = :id');
$stmt->bindParam(':quantity', $quantity);
$stmt->bindParam(':id', $productId);
$stmt->execute();

// 提交事务
$db->commit();

By using transactions, locks, and concurrency control, we This ensures the integrity of the database when users purchase products and avoids any inventory issues.

The above is the detailed content of Advanced PHP database connections: transactions, locks, and concurrency control. For more information, please follow other related articles on the PHP Chinese website!