1

I have a script who does multiple UPDATE on my InnoDB, but if one UPDATE fail, I want all the other UPDATE to be rollback. So I searched over the web and the best I could find was PDO::beginTransaction. Except that I want my DB to be actually updated for every UPDATE and if one of them fail, I need to delete every previous UPDATE. I need this because this script can be run from multiple user, and the previously updated row shouldn't be updated again.

exemple :

here's my table "reserver" :

id | idChambre | idReservation
---+-----------+--------------
0  | 1         | 1

the first row means that the room 1 is matched with the order 1 so it can't be matched again. So if nothing wrong happens this row will stay the same, but if the user cancel or timeout event occurs, I want this row to be deleted.

So now I see 2 way : pdo Transaction (but I don't know how) and my actual way : storing every id of my "reserver" table and delete them all if something wrong occurs.

Is there any better way ? Am I doing it wrong ?



English is not my native language so be nice please.

Hayanno
  • 182
  • 2
  • 8
  • 1
    This is exactly what transactions are for. Time to learn them. They're not difficult. – John Conde Apr 16 '15 at 13:17
  • But does transaction ACTUALLY update my database or is it done (as I read everywhere) when "commit" is called ? – Hayanno Apr 16 '15 at 13:21
  • It's done when you call commit. That's an important part of how transactions work. – John Conde Apr 16 '15 at 13:25
  • I may have express myself poorly but did you understand that I want my row to be ACTUALLY updated BEFORE the end of the script ? I mean that multiple end-user can use my script, so I need to UPDATE my database so a "room" cannot be picked twice, but if something fail later in my script, this row must be deleted. I think pdo transaction make every UPDATE at the end of the script, but in the meantime, another user could have picked the "room" and I would have 2 users with the same "room". – Hayanno Apr 16 '15 at 13:34
  • I don't know how else to explain this but transaction do *exactly* what you need. Read up on them and you'll see that. – John Conde Apr 16 '15 at 13:35
  • I forgot to say that every UPDATE is done using ajax, does something exist who could keep my transaction open while ajax is sending my data to a PHP code that execute every UPDATE ? – Hayanno Apr 16 '15 at 13:57

2 Answers2

0

sorry for writing it here because i don't have much reputation to comment..

1) you should use the procedure where your all update queries should be inside that 2) call the procedure after calling the begin transaction method in above scenario if any query fail to update the rows then your whole procedure would be rolled back 3) you can also use the mysql transaction directly in procedure refer below link for mysql transaction using in procedure level How can I use transactions in my MySQL stored procedure?

Community
  • 1
  • 1
sandeep_kosta
  • 346
  • 1
  • 15
  • but i don't recommend the transaction in procedure level because if you have bunch of procedure so in every procedure you have to implement the same so the best way to use the pdo begin transaction function in the abstraction level which will implement the begin transaction , rollback and commit function and always call the procedure inside that . – sandeep_kosta Apr 16 '15 at 13:36
0

You can achieve this using transaction management. Check the below code:

try {  
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $conn->beginTransaction();
  $conn->exec("first update query");
  $conn->exec("second update query");
  $conn->commit();

} catch (Exception $e) {
  $conn->rollBack();
  echo "Failed: " . $e->getMessage();
}

All the update queries executed after beginTransaction will be rolled back if an error occurs.

Syam
  • 409
  • 5
  • 8