Like i said before, if you have an entity that spans 2+ tables, you're going to need transactions. If you use triggers, you're going to need transactions. If you use foreign keys, you're going to need transactions.
A transaction treats any number of SQL statements essentially as one -- if one fails, they all fail. If they all succeed, you can commit the transaction and have the data written out to the tables. Transactions are heavily used in conjunction with foreign keys, triggers, et al to support DATA INTEGRITY. Sure, MySQL may be quite fast, but if I have to reinvent the wheel of foreign keys and triggers, and the backend DB doesnt help me maintain data integrity, why would I care about how fast my queries are on completely irrelevant and stale data?
For example ( w/o a trigger ): ---[ CUT ]--- BEGIN TRANSACTION; select nextval('trans_seq'); -- store this in a variable in your code
update account set balance=balance+45.00 where account_id=1; COMMIT; ---[ CUT ]---
Lets say the second statement succeeded and the third ( update account ) failed. With transactions, you could NOT commit the data. Without transactions, your database is now shoring erroneous data; the account's balance is incorrect. Your query to pick up the account may be blindingly fast, but your data is erroneous -- and your company just lost $45.00 (in this transaction alone). Now apply this to an e-commerce site, where customers may order well over $1,000 at a time. Would you want to potentially lose $1,000 just so you can have your queries come back real fast, or would you want to sacrifice some speed in order to have your db essentially maintain itself? Bear in mind that I've had numerous queries under PostgreSQL come back much, much faster than they did under MySQL with the SAME DATASET. And PostgreSQL has transactions, views, foreign key support, triggers, etc.
"As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing."
Oh, so you can roll back these atomic operations without having to write additional SQL to do deletes? And you can wait until all your inserts/updates are done before committing the changes to the RDBMS?
Why the world needs transactions: if you have an entity that spans, say, 3 tables, you're going to need 3 different SQL INSERT commands. If one of these commands fails, you need to back out the others. For instance, lets say you successfully inserted into 2 of the 3 tables. Your third insert fails. Without transactions, you have to manually go back and issue potentially expensive deletes. With transactions, you simply issue 'rollback' and poof, your data is back to normal.
It's a little slow to launch applications on a G3/300...
Dude, go get a 400MHz G3 CPU from one of the many sites that sell them. I got mine via http://www.macselect.com. The G3/300MHz only has 512k worth of cache, compared to 1M on the 350s, 400s, etc. Trust me, it makes a big difference.
Slashdot Mirror
Like i said before, if you have an entity that spans 2+ tables, you're going to need transactions. If you use triggers, you're going to need transactions. If you use foreign keys, you're going to need transactions.
A transaction treats any number of SQL statements essentially as one -- if one fails, they all fail.
If they all succeed, you can commit the transaction and have the data written out to the tables. Transactions are heavily used in conjunction with foreign keys, triggers, et al to support DATA INTEGRITY. Sure, MySQL may be quite fast, but if I have to reinvent the wheel of foreign keys and triggers, and the backend DB doesnt help me maintain data integrity, why would I care about how fast my queries are on completely irrelevant and stale data?
For example ( w/o a trigger ):
---[ CUT ]---
BEGIN TRANSACTION;
select nextval('trans_seq'); -- store this in a variable in your code
insert into billing (trans_id, account_id, customer_id, amount, trans_type) VALUES(*my trans_seq*, 1, 1, 45.00, 'D');
update account set balance=balance+45.00 where account_id=1;
COMMIT;
---[ CUT ]---
Lets say the second statement succeeded and the third ( update account ) failed. With transactions, you could NOT commit the data. Without transactions, your database is now shoring erroneous data; the account's balance is incorrect. Your query to pick up the account may be blindingly fast, but your data is erroneous -- and your company just lost $45.00 (in this transaction alone). Now apply this to an e-commerce site, where customers may order well over $1,000 at a time. Would you want to potentially lose $1,000 just so you can have your queries come back real fast, or would you want to sacrifice some speed in order to have your db essentially maintain itself? Bear in mind that I've had numerous queries under PostgreSQL come back much, much faster than they did under MySQL with the SAME DATASET. And PostgreSQL has transactions, views, foreign key support, triggers, etc.
"As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing."
Oh, so you can roll back these atomic operations without having to write additional SQL to do deletes? And you can wait until all your inserts/updates are done before committing the changes to the RDBMS?
Why the world needs transactions: if you have an entity that spans, say, 3 tables, you're going to need 3 different SQL INSERT commands. If one of these commands fails, you need to back out the others. For instance, lets say you successfully inserted into 2 of the 3 tables. Your third insert fails. Without transactions, you have to manually go back and issue potentially expensive deletes. With transactions, you simply issue 'rollback' and poof, your data is back to normal.
Dude, go get a 400MHz G3 CPU from one of the many sites that sell them. I got mine via http://www.macselect.com. The G3/300MHz only has 512k worth of cache, compared to 1M on the 350s, 400s, etc. Trust me, it makes a big difference.