Faking transactions in MySQL

zkent

I need the ability to 'fake' a transaction in MySQL. I have a table of 'instructors' and a table of 'courses' in a typical one-to-many scheme. I want to be able to remove the instructor and all of his related courses. Any thoughts on how to do this?

Zach

ruttiger

i'm guessing you're not looking for a simple couple SQL statements?

delete from courses where instructor_id=$instructorID;

delete from instructor where instrutor_id=$instructorID

again, sorry, i'm sure this isn't what you wanted - if it's not, explain what you're lookin for?

[deleted]

If you really want to fake transactions, just
create a void BEGIN, COMMIT and ROLLBACK function. that's fake..

I guess you want a workaround for transactions.

Save yourself a lot of work and upgrade mysql to the latest version. That will support the berkeley transaction library... and there you have your transactions, real ones.

zkent

What you gave is exaclty what I have but related information spanning many tables, what would happen if the server's power died between deletes or the script bombs and not all related data is removed from the database. I would have lost data out there. "Transactions" are a special feature of advanced databases where a set of actions are grouped together and if any of the actions fail the database reverts to its previous state. Unfortunately, mySQL does not support transactions.

[deleted]

Fortunately, MySQL DOES support transactions.

Anon

try locking the tables first for a workaround to transactions...

example:

LOCK TABLES courses WRITE, instructor WRITE

delete from courses where instructor_id=$instructorID;

delete from instructor where instrutor_id=$instructorID

UNLOCK TABLES

not sure what version this became available tho

hope this helps

[deleted]

This has nothing to do with transactions at all.
Transactions let you UNDO a query, this just stops other people from messing with your data untill you are finished.

And it does it in the worst possible way, using table level locking.

Anon

exactly my point

(best way to lose an argument 🙂)

[deleted]

V for victory! :-)