Understanding Pdo MySQL Transactions

Understanding pdo mysql transactions

You are not going to find the answer in php documentation because this has nothing to do with php or pdo.

Innodb table engine in mysql offers 4 so-called isolation levels in line with the sql standard. The isolation levels in conjunction with blocking / non-blocking reads will determine the result of the above example. You need to understand the implications of the various isolation levels and choose the appropriate one for your needs.

To sum up: if you use serialisable isolation level with autocommit turned off, then the result will be 12000. In all other isolation levels and serialisable with autocommit enabled the result will be 11000. If you start using locking reads, then the result could be 12000 under all isolation levels.

PHP PDO MySQL and how does it really deal with MySQL transactions?

https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html says:

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

So when you set autocommit=0 in a session (call it session 1), this implicitly opens a transaction, and leaves it open indefinitely.

The default transaction isolation level is REPEATABLE-READ. So your session will not see a refreshed view of committed changes from other sessions' work until session 1 explicitly commits or rolls back.

Your LOCK TABLES in another session 2 does cause an implicit commit, but session 1 doesn't see the result because it's still only able to see an isolated view of the data because of its own transaction snapshot.

PDO MySQL Transactions

Transaction per connection. But some queries might be affected by each other from different connections.
For InnoDB connections all queries in transactions. But with autocommit it commits after each of them. That's why you are always in transaction.
You can do that with your own variable... Also you can use autocommit variable to find out that you have started a transaction. You can do it with query select @@autocommit.

PDO transaction functions vs MySQL transaction statements?

From a portability standpoint, you're better off using the interface that PDO provides in case you ever want to work with a different DBMS, or bring on another team member who's used to another DBMS.

For example, SQLite uses a slightly different syntax; if you were to move to an SQLite database from MySQL, you would have to change every string in your PHP code that contains the statement START TRANSACTION; because it would no longer be valid syntax for your database. SQL Server 2014 is another example that doesn't use this syntax.

Of course, you can also use BEGIN; in MySQL to start a transaction, and that would work fine in SQLite and SQL Server. You could just use that instead.

You'll often be able to find a syntax that you like and that is reasonably portable but why spend the time and energy to even think about it if you don't have to? Take advantage of the fact that there are a dozen PDO drivers available to make your life easier. If you care at all about consistency, favor the API over implementation-specific SQL syntax.

PHP - Difference between all PDO transaction and stricly MySQL transaction?

As I read the PDO documentation on transactions, both methods are equivalent. That is, PDO will use the driver-level transaction capabilities which, in the case of pdo_mysql, should be starting a native MySQL transaction.

I think the bigger issue to consider is exactly what you want to achieve. Are you using a transaction for performance reasons, or because the data is related and nothing should be inserted if one statement fails?

If the former, I would recommend benchmarking it. If the latter you need to structure the code such that logically related data are within the same transaction.

Edit in response to question update:

I'm inclined to say that issuing one large SQL statement would be quicker since there should be a bit less network traffic overhead (assuming the web and database servers aren't on the same host). With 'good' hardware and the correct configuration for your application requirements MySQL should be able to handle many thousands of inserts a second.

Of course, without knowing the details of your setup, this is pure speculation. But I suspect this kind of application-level optimisation is much less effective than tuning the database server.

The only way to be sure is to benchmark both ways and see if one is significantly better than the other.

How exactly do transactions with PHP PDO work with concurrency?

Transactions are atomic only with respect to other database connections trying to use the same data, i.e. other connections will see either no changes made by your transaction, or all changes; "atomic" meaning no other database connection will see an in-between state with some data updated and other not.

PHP code between queries won't break atomicity, and it does not matter where you prepare your statements.

PHP, MySQL, PDO Transactions - Can fetchAll() come before commit()?

Your first question:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.

Also worth noting (emphasis mine):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

This can cause racing conditions.

Your second question:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

From the documentation it says:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.

However note the following from the same source as well:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

So be sure to check the compatibility beforehand!

A few notes

Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.

Another note from the documentation:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

PHP/MySQL - How to use MySQL BEGIN/COMMIT with PDO

Transaction syntax:

START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY

BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK
[WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}

Transaction example:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Taken from here.

You intend to create a transaction via PDO. That is not really a problem. You can do it by generating the query text accordingly:

$query = $db -> prepare 
                        ("
                        START TRANSACTION;
                        INSERT INTO chat (chat_id,msg,datetime) 
                        VALUES (:cid,:msg,:datetime)
                        INSERT INTO chat_connect (chat_id,sender_id,receiver_id)
                        VALUES (:cid2,:sender_id,:receiver_id);
                        COMMIT;
                        ");
$query -> execute(array(
            "cid" => $cid,
            "msg" => $msg,
            "datetime" => $datetime,
            "sender_id" => $getid,
            "receiver_id" => $frid,
            "cid2" => $cid
            ));

Here you can see how you can write a bullet-proof transaction.

PHP + MySQL transactions examples

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

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 (\Throwable $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
    throw $e; // but the error must be handled anyway
}

Note that, with this idea, if a query fails, an Exception must be thrown:

PDO can do that, depending on how you configure it
- See PDO::setAttribute
- and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

PHP PDO Transactions?

This function returns primary key of just inserted record: PDO::lastInsertId
You will need it for NEED_USERS_UID_FOR_HERE parameter. Use it just after INSERT statement.

Since you started a transaction, data will not be inserted into any table if any error occures provided you use InnoDB engine for your MySQL tables (MyISAM doesn't support transactions).

Understanding Pdo MySQL Transactions