DB2020FALL/week13.sql at master · bobur554396/DB2020FALL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- Transactions

CREATE TABLE accounts (
  id SERIAL,
  name VARCHAR(255),
  balance NUMERIC
);

INSERT INTO accounts (name, balance) VALUES ('Alice', 10000);

SELECT *
FROM accounts;

BEGIN;
INSERT INTO accounts (name, balance) VALUES ('Bob', 10000);
COMMIT;


BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

SELECT *
FROM accounts;
COMMIT;

INSERT INTO accounts (name, balance) VALUES ('John', 0);

SELECT *
FROM accounts;

BEGIN;
UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1500
WHERE id = 3;

ROLLBACK;

SELECT *
FROM accounts;

BEGIN;
UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

SAVEPOINT save_point_1;

UPDATE accounts
SET balance = balance + 1500
WHERE id = 3;

ROLLBACK TO save_point_1;

UPDATE accounts
SET balance = balance + 1500
WHERE id = 2;

COMMIT;