CODEWITHSUNDEEP
phpmysqltransactions
Kelly Larsen
Kelly Larsen

Reputation: 973

Could simultaneous transactions interfere with each other? php/mysql

So I found this example code and plan to do something like it for a project of mine but there's a concern that's bugging me

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

the front end uses one mysql user (lets say 'webaccess-admin' 'password') to connect to the database, so what happens if two people run the code at once, and it fails for one of them? would an error in the execution of one instance of a php script cause the sql queries of another instance of that script to rollback as well? do I have to use named transactions or something? or separate mysql users?

Here's an example of what I'm talking about:

person1 -> begin transaction

person2 -> begin transaction

//sql code executed by both users

person1 -> commit

person1 [!error]

person2 -> still running queries (or maybe has committed by now)

person1 will trigger a rollback, what happens to the queries that person 2 has run?

Upvotes: 3

Views: 1488

Answers (2)

user1864610
user1864610

Reputation:

Transactions are intended to be atomic, that is, any given process will see all of a transaction, or none of it. In your example, then, an error in person1's transaction will not appear to person2, nor will the rollback affect what person2 does.

There are some caveats, however. For example, the atomicity is only guaranteed for non-mixed table types, and where table engines support it. The full details are available on the MySQL site

Upvotes: 1

Wilson212
Wilson212

Reputation: 563

This depends entirely on the Transaction Isolation that is used by the DB Connection. With php, im pretty sure they dont interfere at all. When making changes to a database via UPDATE and INSERT statements, they usually dont take effect until the transaction is commited.

Upvotes: 0

Related Questions