ROLLBACK & COMMIT question

[Anon]

OK... so I'm learning transactions. I have a question.

I see lots of transactions like this:

BEGIN TRANSACTION;
INSERT INTO myTable (id, value) VALUES (1, 1);
ROLLBACK TRANSACTIon;
COMMIT;

Does this mean taht it will only rollback if ther's an error, or else it'll commit, or does it meanb it'll rollback all the time?

--Jason

[Anon]

The way this is written it will always rollback the trasaction.

[Anon]

So how do I make it so that it would know to rollback the transaction or not? I presume I would use IF or something, but how would I check if there's an error or not?

--Jason

[Anon]

This is something that you'd check in your code. If you catch an error anywhere inbetween your BEGIN and COMMIT statements then you'd need to handle a ROLLBACK.

When you use transactions it's mostly because you are going to perform multiple insert/update/delete statements all at one time and they all need to process correctly or you're data will be left in an inconsistent state (a very bad thing for things like banks). So you'd BEGIN your transaction then run all of your statements checking for errors on each one, if you get no errors at the end you would COMMIT, if you did get an error you'd ROLLBACK.

[Anon]

I understand that... I knew that... what I was wondering was how to set it up so that one can know when to ROLLBACK and when to COMMIT.

--Jason

[Anon]

Well there is no way that I can tell you that since I'm not building your application and I don't know what the criteria is.

But when you run an insert/update/delete statement the PHP code that runs that statement usually returns some type of status flag. So if you are using Postgres as your data base and your run a pg_exec() command it will return a result index or false so you could do something like:

if (pg_exec($conn, "BEGIN TRANSACTION") {
if (pg_exec($conn, "INSERT INTO x VALUES (1)") {
else {
pg_exec($conn, "ROLLBACK");
}
pg_exec($conn, "COMMIT");
}

Or something like that. Read up on the functions for your database and you'll see how you can catch any errors and deal with them correctly.

[smarlowe]

Note that in postgresql and many other dbs, rollback is automatic if any statement generates a data error.

I.e., this:

begin;
select ...
insert ...
delete ...
update ...
exit;

is a perfectly reasonable way of doing an all or nothing transaction in postgresql. If any of the insert, delete, or update statements fail, the whole transaction is rolled back. In other words, you don't need to check for errors on each insert/delete/update, all you have to do is let postgresql handle it.

Of course, error handling is nice, but you don't have to catch the error to prevent data corruption, only for the convenience of your user to stop it from trying to insert the next 10,000 rows after the first one errors out. It sucks to watch 5,000 lines of "transaction aborted, all changes will be rolled back" autogenerated error messages from your PHP/Postgresql app.

[Anon]

Thanks Scott,

I havn't worked with transactions in Postgres yet so I'd never run into this. In fact I haven't worked with transactions for 4 years since I was working with Informix.

[smarlowe]

In postgresql by the way, every delete, insert, or update IS a transaction on it's own if you don't wrap it up in a larger one.

That's why it can be so slow if you do this:

for ($i=0;$<1000;$i++){
pg_exec($conn,"insert into...");
}

but using transactions can speed things up quite a bit. Adding begin end pairs to it like this:

pg_exec($conn,"begin");
for (....) {
pg_exec($conn,"insert...");
}
pg_exec($conn,"end");

makes it much faster.

Pretty much any time I'm changing more than one thing in a database I put it into a transaction for speed and reliability.

Transactions also let you do things like update all the records in a table at once, and then have all the changes show up at the same time so the user never sees incoherent data.