Checking status of a Transaction - MySQL and PHP

Question

I want to use Transactions in MySQL, my questions is that How I can check if the queries are submitted successfully or not? my query is:

mysql_query("START TRANSACTION");
$query_1 = mysql_query("DELETE FROM BLAH WHERE BLAH");
$query_2 = mysql_query("DELETE FROM BLAH WHERE BLAH");
mysql_query("COMMIT");

as far as I know, the above code does the transaction, but I want to check if the queries are submitted successfully or not, so:

    mysql_query("START TRANSACTION");
    $query_1 = mysql_query("DELETE FROM BLAH WHERE BLAH");
    $query_2 = mysql_query("DELETE FROM BLAH WHERE BLAH");
    if($query_1 && $query_2){
       mysql_query(COMMIT);
    }else{
       mysql_query(ROLLBACK);
    }

But I read somewhere that in transactions, the queries submit when we call "COMMIT", so the above code should not work since while we check $query_1 && $query_2, actually nothing is submitted to db since it's before the "COMMIT" query, how I could perform such check?

Thanks in advance

P.S: Am I doing the whole thing right? please kindly let me know if there are also some other better ways... thanks

Check this out - http://stackoverflow.com/questions/13148630/how-do-i-find-which-transaction-is-causing-a-waiting-for-table-metadata-lock-s/13155778#13155778 — Joddy, Dec 22 '12 at 11:25
this too http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples — Pruthvi Nag, Dec 22 '12 at 11:27

Lix · Accepted Answer · 2012-12-22T11:32:22.670

1

If you are performing DELETE commands, you'll be able to see if there was any affected rows by calling the mysql_affected_rows() function.

mysql_affected_rows — Get number of affected rows in previous MySQL operation

There is even mention of use in conjunction with transactions in the documentation -

Note: Transactions If you are using transactions, you need to call mysql_affected_rows() after your INSERT, UPDATE, or DELETE query, not after the COMMIT.

It should be noted here that you are using an old version of database connections. See the php documentation on any page related to mysql_* commands and you'll encounter this message -

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

edited Dec 22 '12 at 11:32

answered Dec 22 '12 at 11:27

Lix

47,311
12
103
131

how would he know that how many rows will be affected so that he can confirm that the transaction has been made successfully? – Pruthvi Nag Dec 22 '12 at 11:28
@pru - The number of rows affected would have to be matched against the number of intended deletions. – Lix Dec 22 '12 at 11:30
@Lix and in general, what if I'm not deleting!? what if I'm just inserting in multiple tables...? what if `AUTOCOMMIT = 0`, where should I use `AUTOCOMMIT = 0`? thanks – behz4d Dec 22 '12 at 11:33
@beh - If you are inserting, then you'd look at [`mysql_insert_id()`](http://php.net/manual/en/function.mysql-insert-id.php). – Lix Dec 22 '12 at 11:34
IF you are looking for general errors you could also take a look at `mysql_error()`. – Lix Dec 22 '12 at 11:35
@Lix my argument is, he may not be able to determine every time how many rows will be deleted depending on the query. so that he can assume that "k 60 rows have been affected as i thought" so the transaction has run fine. – Pruthvi Nag Dec 22 '12 at 11:36
@pru, for that kind of confirmation you would need to do a `SELECT count(*)` with the same parameters to check how many records match the search criteria. – Lix Dec 22 '12 at 11:38
1

@pru - Deleting 0 records because no records were found matching the search criteria could also be a success... It all depends on the context. – Lix Dec 22 '12 at 11:39
@Lix AFAIK transactions are generally used to update or modify data in database not to retrieve. Nothing will stop you to do otherwise. – Pruthvi Nag Dec 22 '12 at 11:40
@Lix haha we should take this to some private chat or chat room but have to leave. – Pruthvi Nag Dec 22 '12 at 11:41

Checking status of a Transaction - MySQL and PHP

1 Answers1