Zeratops Zeratops - 2 months ago 11
MySQL Question

Multiple transactions on a single InnoDB table possible?

This is a question regarding SQL, but the aim is to create a php script.

CONTEXT

Let say I have one database containing 2 tables (set on InnoDB engine) :


  • users

  • cart



Now 2 users access my application (in php), and both launch a web page that is doing the following algorithm :


  1. Open the connection

  2. Open a transaction

  3. Insert a new line in the table user

  4. Commit the transaction



QUESTION

Can SQL handle 2 transactions on the same table ?

PhP Code (for the record)

$host = 'localhost';
$database = 'user';
$user = 'root';
$password = '';
$encode = 'utf8';
$dsn = "mysql:host=$host;dbname=$database;charset=$encode";
$pdo = null;

try {
$pdo = new PDO($dsn, $user, $password);
$query = "INSERT INTO user (name, city) VALUES ('test', 'Paris')";
$pdo->beginTransaction();

if( $pdo->inTransaction() ) {
// Can I still launch the query ?
}
else {
// Or should I stop here ?
}

if( $pdo->query( $query ) ) {
$pdo->commit();
}
else {
$pdo->rollBack();
}
}
catch( PDOException $e ) {
print_r($pdo->errorInfo());
}

Answer

Correct, you can perform multiple queries while in a transaction. This is actually the point of using transactions.

In PDO, from the documentation, as long as you have started a transaction by calling PDO::beginTransaction(), you may perform multiple queries, and the changes are not seen by anyone else until you are finished (i.e. by calling PDO::commit()).

You're not limited to making updates in a transaction; you can also issue complex queries to extract data, and possibly use that information to build up more updates and queries; while the transaction is active, you are guaranteed that no one else can make changes while you are in the middle of your work. For further reading on transactions, refer to the documentation provided by your database server.

Using InnoDB engine in MySQL, specifically, you have row-level granularity, which means only rows are locked, not the entire table like in (older) MyISAM engine.