Transaction Behavior please explain

So I’m trying to program a transaction using Doctrine DBAL and I wasn’t able to make it work as I expected so I decided to go directly into Mysql and try my luck directly into the beast but I’m at a lost.

Here is what I am querying directly into Mysql


START TRANSACTION;
Select id,ip from member;
Update member set ip=888 where id=120;  -- non existent row
Update member set ip=888 where id=0;      -- existent row
-- Update member set isdfsdfp=666 where id=0; -- non existent column
Select id,ip from member;
Commit;

The problem here is that the row with id=120 does not exist. Yet when the above is run the commit is successful.
Interestingly also if I un-comment the last update line (which updates an non-existent column) then the code just stops it will commit the first part but it won’t preform the last select.

I’m starting to wonder what the point is of transactions if they don’t work?

It looks like you may need the ROLLBACK statement

MySQL Transaction Gotchas & Good Parts » SitePoint