acct | vault | pouch -------+-------+------- jen | 42 | 0 stu | 1000 | 0 aja | 1000 | 0 aal | 1000 | 0 sba | 1000 | 0 mbo | 1000 | 0 obu | 1000 | 0 fca | 1000 | 0 mch | 1000 | 0 ach | 1000 | 0 kgo | 1000 | 0 mhu | 1000 | 0 shu | 1000 | 0 aka | 1000 | 0 mmi | 1000 | 0 out | 0 | 0 smu | 1000 | 0 apa | 1000 | 0 apo | 1000 | 0 hrh | 1000 | 0 rta | 1000 | 0 stu | 1000 | 0 svo | 1000 | 0 (23 rows)In each part of the lab, you will try the following transactions
Please sit around a table with others, and
introduce yourself to your neighbors. Using the default
"autocommit" (each SQL statement is a transaction by itself),
first SELECT * FROM acct; and then figure out how to
update the balances of yourself and your neighbor on your right, or the bank, and
try the two required updates, for instance,:
UPDATE gwb SET vault=880 WHERE acct='jen';
UPDATE gwb SET vault= 120 WHERE acct='out';
--
this one should abort
Discuss with everybody what is going on. Any money lost or
gained? This schedule is subject to change depending upon what
happens:
Now I will attempt to bring the table to a consistent state
(probably the initial one) and you will please be careful to use
transactions. A transaction starts with begin, and ends with
either commit or rollback (if you don't want to go through with
it), try both. Like this:
BEGIN;It is possible that some UPDATE operations will hang. This means another transaction has updated the same row, but has not yet committed or rolled back. You will have to wait, during which time you may yell at your classmates to please make up there minds!
SELECT * FROM gwb;
UPDATE ... ;
SELECT SUM(vault)-21000 AS net FROM gwb; (this will be non-zero, but invisible to others!)
UPDATE ... ;
SELECT * FROM gwb; (optional)
SELECT SUM(vault)-21000 AS net FROM gwb; (this should again be zero)
ROLLBACK; ( or COMMIT; -- you decide)
Of course, figuring the new balance on paper is a bit of a
problem. A better way is to let the server add or subtract, as
mentioned in the users guide:
BEGIN;
UPDATE gwb SET vault = vault + 100 WHERE acct = 'jen';
UPDATE gwb SET vault = vault - 100 WHERE acct = 'out';
COMMIT;
"If two such transactions concurrently try to change the balance of account 'bank', we clearly want the second transaction to start from the updated version of the account's row. Because each query is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency." (end quoting the users guide)
Try a transaction that will make some account negative. What
happens?
is any money lost (after it is rolled back?)
For all transactions such as these, which make linear
changes, read committed should behave correctly. If not, we will
try again with serializable.
Now we will try the same thing with serializable transactions. At
some point I will start a non-linear transaction, adding
interest to some accounts. This may delay you further. As long as
everyone's transactions are properly done, there should be no
inconsistencies. However, many transactions may have to be rolled
back. To set this isolation level for all transactions, or for
each individual transaction:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- will only apply to this one
...
COMMIT;
Well, did anybody succeed? How many transactions aborted? Did you
have to type "rollback;" to try again?
On the bright side, is the database in good order still?
Again according to the documentation, isolation level serializable is most commonly appropriate when a transaction will do multiple queries and requires an absolutely consistent view of the database. Using the initial "paper" method of figuring new balances may need this. The problem is that many transactions may get aborted.