# Homework 1

**Deadline:** 30.10.2017, 21:59

**Points:** 1

Create a table `accounts` with two columns, `aid` and `balance`, both of
type `integer`. Insert two tuples: `(1, 1000)` and `(2, 2000)`.

Execute the following transactions (imitating two concurrent money
withdrawals from the same account) and give answers to **Questions**
**(a)**-**(h)**.

Before updating the balance with the new value, the transactions verify if
there is enough money on the account. After withdrawal, the new amount is
also verified. These tasks are imitated by the `SELECT` statements in steps
3, 4, 7, and 9.

Submit your answers through the submission system as a single `.txt` file.

```
    T1 (withdraw 200):            T2 (withdraw 1000):
1   BEGIN;
2                                 BEGIN;
3   SELECT *
    FROM accounts
    WHERE aid = 1;
4                                 SELECT *
                                  FROM accounts
                                  WHERE aid = 1;
5   UPDATE accounts
    SET balance = 800
    WHERE aid = 1;
6                                 UPDATE accounts
                                  SET balance = 0
                                  WHERE aid = 1;
7   SELECT *
    FROM accounts
    WHERE aid = 1;
8   COMMIT;
9                                 SELECT *
                                  FROM accounts
                                  WHERE aid = 1;
10                                COMMIT;
```

## Questions

### Question (a)

What are the values seen by `T1` in step 3 and `T2` in step 4?

### Question (b)

After step 10, is the update made by `T1` in step 5 reflected on the database
or is it lost?

### Question (c)

What happens after step 6?

### Question (d)

What are the values seen by `T1` in step 7 and `T2` in step 9? Is the balance
of the account correct with respect to intended withdrawal tasks?

### Question (e)

What happens to `T1` and `T2` if `T1` rolls back in step 7?

### Question (f)

After swapping steps 4 and 5, does `T2` see the value updated by `T1`?
Why is that so?

### Question (g)

What happens if we partially modify the UPDATE statements with
`SET balance = balance - X` where `X = 200` for `T1` and `X = 1000` for `T2`?
Is the balance correct after the transactions commit?

### Question (h)

How to fix the scenario to disallow a negative balance?