Transactions and table locking

[RobMs]

Hey all,

 

I know PDO transactions are ACID. However does this mean that others cannot read/write to the database during a transaction? Or should I use table locking with transactions for this?

 

What I need to do is: be able to rollback if one of the transactions does not execute correctly and also prevent others reading/writing to the database until the transaction is commited. So should I use transactions and table locking? or will transactions do on its own?

 

Thanks,

[Mchl]

PDO transactions are ACID compliant, if and only if the database engine you're using is ACID compliant (in MySQL's case it's InnoDB as of now).

If it's ACID, it takes care of all the locking by itself, so you do not need to do anything apart from starting a transaction and commiting/rolling back it.

[RobMs]

Great thanks!

 

Is it possible to rollback a statement in a function?

I seem to be having difficulties, I purposely made the statement erroneous, but it still writes the first statements (Im using InnoDB so that is not the problem) I can roll back errors in the non function statement (the function is a new addition).

 

example

 

try:

transaction begin

sql statement

sql statement

sql statement

function(thisfunctionrunssqlstatement)

 

commit transaction

 

catch:

exception

rollback transaction

 

 

Any help much appreciated, thanks

[Mchl]

I have never actually used PDO (I stick to ext/mysqli), so I am not sure, but there should be no problems with that (as long as you do it within one connection).

Remember that there are some SQL statements, that do implicit commit:

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

[RobMs]

I think that it is a problem (that i create a new PDO object (connection) in the function).

However when I remove it and try to use the existing I get an error, the function is in a different file, but its php included into the current file.

[Mchl]

You should pass the original PDO object to a function in an argument

 

function doSomething(PDO $pdoObject, $arg1, $arg2...) {
  // do something with $pdoObject;
}

[RobMs]

Thanks again for a reply, but I tried that and it still didnt work. Here is example code:

try {
	$db1 = new PDO('mysql:host='.DB_HOST.';dbname='.DB_DATABASE.'', DB_USERNAME, DB_PASSWORD);
	$db1->beginTransaction();

	[sql STATEMENT]
	[sql STATEMENT]
	[sql STATEMENT]
	[sql STATEMENT]

	$variable = "SOME TEXT";
	action($db1, $variable);				
	$db1->commit();			
	$db1 = null;	
}
catch(PDOException $ex)
{
	$db1->rollBack();
	$db1 = null;
	echo $ex->getMessage();
}

/*function in another file which is included at the beginning*/

function action(PDO $db1, $variable){
	$sql = $db1->exec("SOME STATEMENT BEING DONE WRONG ON PURPOSE using $variable");		
}

 

I tried using global $db1 too in the function, still doesnt work.

To test it I am using an insert statement that tries to insert an id into the database where the id conflicts with an existing primary key. But of course it doesn't rollback previous statements, it doesnt seem to catch it as an error.

[RobMs]

Any ideas?