[RESOLVED] Should you use mysqli prepared statements and transactions together?

[EcceVery]

Like the title says, should you use prepared statements and the commit/rollback approach together for an ordinary series of DB queries?

I use prepared statements all the time but lately I've been thinking this may not be the intended way. I do pretty simple db queries and never really reuse a prepared statement more than once or twice in the same script.

I started looking at transactions, and it seems no examples or scripts I find on the net uses prepared statements and transactions together. All examples uses mysqli->query() and perhaps the real_escape_string() which to me feels old-school.

I was thinking something like this:

$stmt = $mysqli->prepare("INSERT INTO table1 ............");
		$stmt->bind_param("ii", $var1, $var2, .....);
		$stmt->execute();
		$stmt->close();

	if($mysqli->sqlstate != "00000")
		$mysqli->rollback();

$stmt = $mysqli->prepare(" INSERT INTO table2.....)";

Thoughts?

[laserlight]

I do not see what would be wrong with using prepared statements with transactions since they are orthogonal in use.

[Weedpacket]

If the transaction is only going to contain one statement, then there is little point in explicitly starting/committing one: the dbms effectively does all that for every individual statement that isn't already part of a transaction (if the statement errors, then there is nothing to roll back). A transaction is more useful when there are several statements that all have to succeed or else none of them should (those that succeeded prior to the error occurring get rolled back).

That said, explicitly starting a transaction and explicitly committing it even when there is only one statement involved can offer a bit more control at the application level (it makes it possible for the application to "change its mind" and roll back the statement even if it succeeded), and is one less thing to remember to do if additional dependent statements are added later.

In relation to the precise question here; laserlight's reply covers it: it's feasible to use transactions and prepared statements together, or use one but not the other, or use neither. They address different aspects of the process of interacting with the database.

[EcceVery]

I can't get this to work the way I want. It's a really simple job actually:

1. Register a webshop order in the "orders" table. This table contains general information about the order, like who made the order, date and stuff like that. When this is done I use the auto_incremented ID ($oid) for step 2:

2. Loop: Register each product ordered in the "orderrow" table.

This is where I whant to use transactions. Either step 1 and the loop in step 2 all works out, or none should be registered. This is how I tried it:

1. Connect
2. SEt autocommit FALSE
3. do step 1
4. check mysqli->sqlstate, if not "00000" then rollback
5. Step 2 width sqlstate checks for each loop

However it registers step 1 immediately, it does not wait for my commit command. I use phpmyadmin to look at the database and I can see the record there even if I die() the script before it is finished. Is that normal?

I'm not sure how the transaction log really works. I thought all transactions where written to the log, and thereby not visible from e.g. phpmyadmin, and then when the commit() was run it was transferred to the database and cleard from the transaction table. Wrong?

Here's a code example from my DBLayer class (autocommit already set to FALSE via constructor):

public function saveOrder(order $order)
	{
		$uid = $order->getUID();
		$timestamp = $order->getDate();

	$stmt = DBLayer::$mysqli->prepare("INSERT INTO orders SET uid = ?, date = ?, status = 'new'");
	$stmt->bind_param("ii", $uid, $timestamp);
	$stmt->execute();
	$oid = $stmt->insert_id;
	$stmt->close();

	if(DBLayer::$mysqli->sqlstate != "00000")
	{
		# error handling, rollback, then return errorcode
	}

	# Save orderrows
	$orderrows = $order->getOrderRows();
	for($i = 0; $i < count($orderrows); $i++)
	{
		$price = $orderrows[$i]->getPriceIncVAT();
		$count = $orderrows[$i]->getCount();
		$pid = $orderrows[$i]->getPID();

		$stmt = DBLayer::$mysqli->prepare("INSERT INTO orders_row SET oid = ?, pid = ?, count = ?, price = ?");
		$stmt->bind_param("iiid", $oid, $pid, $count, $price);
		$stmt->execute();
		$stmt->close();

		# Same error check here then...
	}

	return $oid;
}

I've make an error in the qsl query in the loop (step 2) to check, and step 1 was not rolled back.

[laserlight]

Maybe you should test by directly using the MySQLi extension instead of your wrapper class, just in case it turns out to be a bug with your wrapper class.

[Weedpacket]

Is there an option to explicitly BEGIN TRANSACTIONs (it should happen implicitly when autocommit is off, but it wouldn't hurt to be explicit)? And the obvious question (for others who have the same problem to check): does the table type being used support transactions?

[EcceVery]

Is there an option to explicitly BEGIN TRANSACTIONs

No. It's done by autocommit(). It seems somewhat backwards to me, but when turned off each request is written to database as the script runs.

does the table type being used support transactions?

InnoDB tables.

[Weedpacket]

That does sound completely backwards: I'm scared to ask if turning it on makes it work as though it's off....

I'd have to side with laserlight on this one: I'm suspecting the wrapper class as well. Maybe that's issuing a commit somewhere between executing the first statement and rolling back (first place I'd look would be that close() method).

[EcceVery]

I think I've got this now. Here's a minimal working example on a table width two columns "a" and "b" (varchars, null allowed):

<?php

$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->autocommit(FALSE);

$stmt = $mysqli->prepare("INSERT INTO test SET a='testa1'");
$stmt->execute();
$stmt->close();

$stmt = $mysqli->prepare("INSERT INTO test SET a='testa2', b='testb2'");
$stmt->execute();
$stmt->close();

$mysqli->commit();
$mysqli->close();

echo "Done.";

?>

The script above will execute without errors. However NOTHING will be done if you do not call commit(). I was wrong about that in the previous post.

You don't have to call rollback() yourself, and thereby no need to check for errors. Rollback is either done by default on fatal errors, or it it not needed since nothing it actually written to the database. You only have to call rollback() if you're making your own error control and whant the script to manually reverse something.

[Bjom]

since with your wrapper class you dont show us how you actually call it, i can only guess: the autocommit(false) command might be in the wrong place (i.e. the stmt is already on the server with autocommit still set to true.)

@: no need to set it to true, that's the default. All it says is: no need for an explicit commit.

the question remains, why you want to use a transaction in the first place? the only need for that is when you are concerned about consistency of more than one query. Standard example: transfer cash from account A to account B:
Qry1: reduce cash on A
Qry2: add cash to B

If two fails the cash is lost! So you need to 'rollback' qry 1.

If you only access one table in one qry, then if the qry fails: nothing happened, so what could you want to rollback?

regards

Bjom

[laserlight]

Bjom wrote:

since with your wrapper class you dont show us how you actually call it, i can only guess: the autocommit(false) command might be in the wrong place (i.e. the stmt is already on the server with autocommit still set to true.)

There is a reason why this thread was resolved, so you might want to read post #9 for the resolution.

Bjom wrote:

the question remains, why you want to use a transaction in the first place?

Atomicity: if any insert fails, none of them should succeed.

[Weedpacket]

Bjom wrote:

@: no need to set it to true, that's the default. All it says is: no need for an explicit commit.

I know. Doing it explicitly shouldn't change the behaviour; if it did then that would be suggestive (see my posts #6 and #8).

[Bjom]

@: yeah yeah you're right :o, but i was tired and not paying attention and jupiter was eclipsed by mars and... 🙂

atomicity: yes, but still, when there is only one insert that's atomic w/o transaction.

[laserlight]

Bjom wrote:

atomicity: yes, but still, when there is only one insert that's atomic w/o transaction.

That's right, but here there are two.

[Bjom]

:eek:

guess I should have done as you had suggested: read post #9.

sry