How do detect that transaction has already been started?

by

The framework has no way of knowing if you started a transaction. You can even use $db->query('START TRANSACTION') which the framework would not know about because it doesn’t parse SQL statements you execute.

The point is that it’s an application responsibility to track whether you’ve started a transaction or not. It’s not something the framework can do.

I know some frameworks try to do it, and do cockamamie things like count how many times you’ve begun a transaction, only resolving it when you’ve done commit or rollback a matching number of times. But this is totally bogus because none of your functions can know if commit or rollback will actually do it, or if they’re in another layer of nesting.

(Can you tell I’ve had this discussion a few times? 🙂

Update 1: Propel is a PHP database access library that supports the concept of the “inner transaction” that doesn’t commit when you tell it to. Beginning a transaction only increments a counter, and commit/rollback decrements the counter. Below is an excerpt from a mailing list thread where I describe a few scenarios where it fails.

Update 2: Doctrine DBAL also has this feature. They call it Transaction Nesting.

Like it or not, transactions are “global” and they do not obey object-oriented encapsulation.

Problem scenario #1

I call commit(), are my changes committed? If I’m running inside an “inner transaction” they are not. The code that manages the outer transaction could choose to roll back, and my changes would be discarded without my knowledge or control.

For example:

  1. Model A: begin transaction
  2. Model A: execute some changes
  3. Model B: begin transaction (silent no-op)
  4. Model B: execute some changes
  5. Model B: commit (silent no-op)
  6. Model A: rollback (discards both model A changes and model B changes)
  7. Model B: WTF!? What happened to my changes?

Problem scenario #2

An inner transaction rolls back, it could discard legitimate changes made by an outer transaction. When control is returned to the outer code, it believes its transaction is still active and available to be committed. With your patch, they could call commit(), and since the transDepth is now 0, it would silently set $transDepth to -1 and return true, after not committing anything.

Problem scenario #3

If I call commit() or rollback() when there is no transaction active, it sets the $transDepth to -1. The next beginTransaction() increments the level to 0, which means the transaction can neither be rolled back nor committed. Subsequent calls to commit() will just decrement the transaction to -1 or further, and you’ll never be able to commit until you do another superfluous beginTransaction() to increment the level again.

Basically, trying to manage transactions in application logic without allowing the database to do the bookkeeping is a doomed idea. If you have a requirement for two models to use explicit transaction control in one application request, then you must open two DB connections, one for each model. Then each model can have its own active transaction, which can be committed or rolled back independently from one another.

More Related Contents:

  1. Understanding pdo mysql transactions
  2. php, mysql – Too many connections to database error
  3. PDOException SQLSTATE[HY000] [2002] No such file or directory
  4. PHP PDO: charset, set names?
  5. How can I use PDO to fetch a results array in PHP?
  6. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  7. What is the difference between MySQL, MySQLi and PDO? [closed]
  8. MySQL check if a table exists without throwing an exception
  9. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  10. PHP PDO – Num Rows
  11. How to solve General error: 2006 MySQL server has gone away
  12. Replacing mysql_* functions with PDO and prepared statements
  13. How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework
  14. PDO + MySQL and broken UTF-8 encoding
  15. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  16. php code to test pdo is available?
  17. PDO fetchAll group key-value pairs into assoc array
  18. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  19. Setting a connect timeout with PDO
  20. What is the best way to insert multiple rows in PHP PDO MYSQL?
  21. PDO MySQL: Insert multiple rows in one query
  22. How to bind LIKE values using the PDO extension?
  23. Error while using PDO prepared statements and LIMIT in query [duplicate]
  24. PDO prepared statement fetch() returning double results
  25. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  26. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  27. PHP 7 RC3: How to install missing MySQL PDO
  28. Selecting all fields except only one field in mysql [duplicate]
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO and MySQL ‘between’

Leave a Comment