Mysql – Do queries called from within a transaction but found within another method act the same

MySQLPHPtransaction

Imagine the following code:

class foo {

    public function bar() {

        // Start the transaction
        $transaction = $this->db->beginTransaction();       

        try {

            // Get our member data
            $member_data = $this->getMemberData();

            // Update users details
            $sql = $this->db->createCommand("UPDATE users SET......");
            $sql->execute();

            // Now let's commit our data
            $transaction->commit();

        } catch (\Exception $e) {

            // Let's rollback the transaction
            $transaction->rollBack();           

        }       

    }

    public function getMemberData() {

        $sql = $this->db->createCommand("SELECT * FROM users WHERE id=123 LIMIT 1 FOR UPDATE");
        $member_data = $sql->queryOne();

        return $member_data;

    }

}

Now, does the query within the getMemberData method act as part of the transaction since it is being called within the transaction and it doesn't matter to the database how it is being called since we have a transaction open or am I required to move the query inside the transaction code block?

Best Answer

MySQL does not care about your PHP code structure. One transaction is what is between start and commit/rollback, sent over one connection - thats important, if your $this->db->createCommand()->execute/query creates a separate connection for some reason then it won't work but that should not be the usual case, just mentioning it because it sometimes happen so better to check.

You can have multiple transaction "open" using multiple connections but then you can deadlock yourself so beware (MySQL can automatically resolve deadlocks on the DB layer but not if you lock a record from one connection and then try to access it from some other, because your app is single-threaded (probably) and the deadlock detection expects parallel clients.

Related Question