CODEWITHSUNDEEP
phpmysqltransactions
Eae
Eae

Reputation: 4321

MySQL ROLLBACK and use with PHP

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1 FOR UPDATE;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

I have taken this query directly from the MySQL official documentation and I am wondering certain aspects (I added myself the FOR UPDATE). Supposing that there is another user that already has a lock on the record. In that case the update should fail. If that were to happen I am wanting for sure a way to display to my user a message box that will alert the user that there is a lock on the record. How might I get that information back into PHP? Also wouldn't this query roll back by default or do I specifically need to add the ROLLBACK?

Thanks in advance!

Upvotes: 1

Views: 199

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

You have to reset the value of autocommit variable to OFF / 0 / FALSE. Try this: 

SET SESSION autocommit = 0;
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1 FOR UPDATE;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Upvotes: 1

Related Questions