List:General Discussion« Previous MessageNext Message »
From:Fred Lindberg Date:April 27 1999 8:47pm
Subject:Re: transaction support
View as plain text  
On Tue, 27 Apr 1999 14:22:37 -0600, Sasha Pachev wrote:

>the time an application that relies on transactions does
>not care to see the results of its work until it commits
>anyway. What do you guys think, is this something
>worthwhile or not?

Most of the time you care about if the individual steps of the
transaction worked. So with transactions:

_Start
remove $50 from checking
add $50 to savings
_commit
if (error) error message;
success;

This either works or not. I can assume that there will never be a case
where $50 has been removed from checking and not applied to savings.

Without transactions:

UPDATE checking where id=x and amount>50 set amount=amount-50;
if (affected_rows=0) {"sorry, you're out of cash"; fail;}
if (error) { error message; fail;}
UPDATE savings where id=x set amount=amount+50;
if (error) {
	UPDATE checking where id=x set amount=amount+50;
	if (error) fail_catastrophic;
	error message;
	fail;
}
success;

LAST_INSERT_ID can be handled by locking the table and I think there is
also atomic support of type "last_insert_id + 1".

The UNIQUE violation can be handled with a:
LOCK TABLE ...;
do stuff
INSERT ...
if UNIQUE violation undo stuff;
UNLOCK TABLES;

Often, restructuring allows you to do the potentially bad insert first
eliminating the need to undo. In other cases, an extra column will
allow you to do inserts, then activate via this column only once
everything worked. Then someone can do cleanup of old non-confirmed
entries.

Another place to use transactions is when you want to insert an item
with properties. If insertion of the item fails, you roll back the new
properties. However, what is the cost of a few table entries? Just
insert the poperties, then the item, then the entries in the link
table.

In general, it's just more work in the application to be traded off
against the overhead [for all work] of having transaction support in
the server. Same for referential integrity: It's work for the app to
enforce it where needed, and work for the server to enforce it
generally. The problem with doing it server side is that is slows down
all the work that doesn't need transactions/referential integrity.


-Sincerely, Fred

(Frederik Lindberg, Infectious Diseases, WashU, St. Louis, MO, USA)


Thread
transaction supportDavid Wall27 Apr
  • Re: transaction supportSasha Pachev28 Apr
    • Re: transaction supportNem W Schlecht28 Apr
    • Re: transaction supportThimble Smith28 Apr
Re: transaction supportFred Lindberg28 Apr
Re: transaction supportFred Lindberg28 Apr
  • Re: transaction supportVolker Paepcke29 Apr
Re: transaction supportFred Lindberg29 Apr