-
Notifications
You must be signed in to change notification settings - Fork 57
/
s8.sql
32 lines (27 loc) · 1.33 KB
/
s8.sql
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
-- examples on transaction isolation levels
-- cleanup
delete from service where service_id = 22;
select * from service;
-- both transactions serialized, no phantom read
begin transaction isolation level serializable; -- (1)
select * from service; -- (2)
-- insert into service (service_id, name) values (22, 'T2 insert'); -- (3) T2
select * from service; -- (4) T1 won't see the phantom
commit; -- (5)
select * from service; -- (6) both T1 and T2 see the change
-- non-repeatable read, when level is repeatable read
begin transaction isolation level repeatable read; -- (1)
select * from service where service_id = 22; -- (2)
-- update service set name = 'T2 update' where service_id = 22; -- (3) T2 update the row
-- commit; -- (4) executed by T2
select * from service where service_id = 22; -- (5) if T1 sees a change here, it is a non-repeatable read!
commit; -- (6) executed by T1
select * from service where service_id = 22; -- (7) both T1 and T2 see the change
-- lost update
set transaction isolation level read uncommitted; -- (1)
start transaction; -- (2)
update service set name = concat(name, '1') where service_id = 22; -- (3) T1
-- update service set name = concat(name, '2') where service_id = 22; -- (4) T2
commit; -- (4)
select * from service where service_id = 22; -- (5) both 1 and 2, no lost update!
commit;