Transactions

create table account (
account_no integer;
owner char(20),
balance float,
primary key (account_no));

insert into account values (1111,'Smith',500);
insert into account values (1112,'Smith',600);

i.e. Customer "Smith" has two accounts; 

     Account 1111 with balance 500
     Account 1112 with balance 600.

To qualify for a loan, a customer must have
at least $1000 in all of his accounts.

Process I (Transfer Money)
s1:  update account set balance = balance - 400
     where account_id = 1111;
s2:  update account set balance = balance + 400
     where account_id = 1112;

Process II (Credit Check)
t1:  sum = 0.0;
t2:  select balance into :b where account_id = 1111;
t3:  sum = sum + b;
t4:  select balance into :b where account_id = 1112;
t5:  sum = sum + b;
t6:  if (sum < 1000) DENY LOAN else AUTHORIZE LOAN;

The execution sequence: s1;t1;t2;t3;t4;t5;s2;t6
will result in a DENY LOAN situation, which is erroneous.

• Transaction System ensures such problems do not arise.
• A transaction is a set of statements which are guaranteed to execute as a whole without interference from the effects of other transactions.
• Locking mechanism is employed by the system.
• A transaction begins with a BEGIN WORK statement
• A transaction ends with either COMMIT WORK or ROLLBACK WORK statement.

BEGIN WORK
LOCK TABLE account
UPDATE account SET balance = balance - 400
WHERE account_id = 1111;
UPDATE account SET balance = balance + 400
WHERE account_id = 1112;
COMMIT WORK;