Failed Deletes, Inserts, updates and database anomalies

[keldorn]

I tried searching for this, but could not find anything.  What if your application is very Acid, and say what happens if there is failed Query that could cause some serious database anomalies,  what would be best to deal with that?

 

 

Take for example you have 2 categories,  Foo and Bar,  Foo and bar ave each 1 Widget, in the table for the categories it lists how many widgets it has, so it would be

 

Foo (1)

bar (1)

 

Now say you do a SQL query and move the widget from bar to Foo, so in the database you update the widgets table and point its category to foo, then you update categories to decrement bar by 1 and increase Foo by 1, but here what happens is that query fails becuase at the point their some network outage on that remote mysql server, or mysql is too busy and accidental drops the UPdate or it get lost or something etc., and to make matters worse you have and die() on that query, so now    Both widets are listed in Foo , and the whole script process dies and it didn't update the categories, now you have an anomaly silently away in your DB!

foo (1)

bar (1)

 

But it should be

 

Foo (2)

Bar (0)

 

What would could be done to prevent this? Would you catch the failed queries and re-run them? or roll back the queries?

 

[trq]

You could use PDO's transactions.