Is there any way to test a transaction to ensure that it will rollback if something fails? I've removed a parameter or misspelled a variable thinking that might do it. Is this not correct, or will a transaction only fail if something deeper occurs?

So far the only conclusions I'm coming up with is I'm either building my transactions wrong, or don't really understand how this process works.

I can post up code, but I thought I'd just ask the general question first. Thanks.

    If you want to force a rollback, why not simply disconnect from the server without COMMIT'ing the transaction? Or simply issue a ROLLBACK statement?

      Ok. That makes sense now that you say it. I apparently have built the transaction all wrong. I added a = NULL before COMMITing and all three of my updates and inserts execute without stopping.

      So, that leads me to my next question. How do you create the transaction if I'm using the class below to handle the connection to the database?

      class PDODB
{
	private static $instance = null;
	public static function get()
	{
		if(self::$instance == null)
		{
			try
			{
				self::$instance = new PDO('mysql:host=localhost;dbname=theDB', 'root', 'root');
			} 
			catch(PDOException $e)
			{
				// Handle this properly
				throw new Exception('DATABASE ERROR: ' . $e->getMessage());
			}
		}
		return self::$instance;
	}
}

      This is how my queries look more or less without the need of a transaction.

      $sth = PDODB::get()->prepare("INSERT INTO table (field1, field2, field3, field4)VALUES (?, ?, ?, ?)");
$params = array($var, $var, $var, $var);
$sth->execute($params);

      This is how I thought I should handle the queries with a transaction. Keeping in mind there would actually be three or four similar queries below.

      try {
	$pdo = PDODB::get();
	$pdo->beginTransaction();

$sth = $pdo->prepare("INSERT INTO table (field1, field2, field3, field4)VALUES (?, ?, ?, ?)");
$params = array($var, $var, $var, $var);
$sth->execute($params);

$pdo->commit();
}
catch(PDOException $e) {
	$pdo->rollBack();
	throw new Exception('DATABASE ERROR: ' . $e->getMessage());
}
        gwerner;10999590 wrote:

        I added a = NULL before COMMITing and all three of my updates and inserts execute without stopping.

        What do you mean "a = NULL" ? Can you show us an example of what you tried?

        If the queries "execute without stopping" and were successful, then that implies that no error has occurred... thus, the transaction was successful and no rollback should occur.

        gwerner;10999590 wrote:

        So, that leads me to my next question. How do you create the transaction if I'm using the class below to handle the connection to the database?

        Your try/catch block in the last code snippet you posted looks like it should do what you want.

          I tried two different things actually. I added both of these lines separately before the commit. They both executed all of my query statements. Maybe this isn't what you were describing above?

          $sth = NULL;
          die();

            If you set $sth equal to NULL, that shouldn't have any effect at all on the transaction. After all, why would it? It doesn't matter whether you've got a statement resource ready to be executed or not... all that matters is the statements that have already been executed as part of the transaction.

            If you call PHP's [man]die/man, then you're going to kill the script's execution at that point. If you do this before any COMMITs, then the DBMS should automatically issue a ROLLBACK when it sees that the client (your script) disconnected with an ongoing transaction. If this isn't happening (e.g. you can actually see the INSERT'ed rows in the DB despite the fact that you never COMMIT'ed them), then something is wrong.

            For example, you never check if PDO::beginTransaction() returns boolean TRUE. If it doesn't, then you're probably still in autocommit mode and thus all of your statements are being executed and COMMIT'ed simultaneously. In that event, you'd want to try and get more information from MySQL as to why the transaction didn't start successfully.

              Ok. The comments about NULL make sense as well. I was basically trying everything to get this thing to fail. It works now. I made the classic mistake. All of my tables were set to InnoDB except one. One lousy table was somehow set to MyISAM. Arrrggh! After your last comment, I went back to check that because that's the first thing you always read. Are your tables set to InnoDB?

              I feel better now knowing that what I'm doing is ok. Because this was driving me mad. I've been reading and googling trying to figure this out. I really appreciate you taking the time to at least glance at my code and verify I'm doing this mostly right. Sorry it was a bonehead mistake ultimately.

                gwerner;10999599 wrote:

                Are your tables set to InnoDB?

                I was actually going to explicitly ask that as well, but I decided to instead suggest a more general "What does MySQL say?" approach since that would hopefully expose that issue as well as any others. :p Either way, sounds like you figured it out now, yes?

                Note that one test you could try is to INSERT multiple rows into a table with any PRIMARY or UNIQUE constraint and explicitly specify the same value for all of the rows in the transaction. The constraint violation(s) should cause the transaction to fail and thus be rolled back.

                Another example, calling die() before you commit() (or simply not commit()'ing at all) should prevent even valid transactions (e.g. transactions that haven't caused any errors yet) from modifying the DB.

                  Yes. This case seems solved now. I called the die() before commit and it does exactly what it should, nothing. Nothing is committed and it rolls back. I will also try your other method just to see what that does out of curiosity. Big thanks again.

                    Write a Reply...