Why is serializable needed in ticket booking?

50 views Asked by At

In this article https://kkyr.io/blog/serializability-vs-linearizability/#:~:text=Banking%20example&text=The%20banking%20system%20uses%20a,and%20does%20not%20guarantee%20linearizability.

The author explains that serializable isolation level is needed to avoid the following scenario

time 1 Alice start transaction select ticket_avail from concert where id = 1
time 2 Bob start transaction select ticket_avail from concert where id = 1
time 3 Alice update concert set ticket_avail = ticket_avail - 1 where id = 1
time 4 Bob update concert set ticket_avail = ticket_avail - 1 where id = 1

This is a race condition if there is only one ticket, so isolation level of serializable is needed

But isolation level only applies to select, all update auto locks the row and examine the latest committed value

https://www.postgresql.org/docs/current/transaction-iso.html

So the following will work

time 1 Alice start transaction 
select ticket_avail from concert where id = 1
time 2 Bob start transaction 
select ticket_avail from concert where id = 1 
time 3 Alice 
update concert set ticket_avail = ticket_avail - 1 where id = 1 and ticket_avail > 0
time 4 Bob 
update concert set ticket_avail = ticket_avail - 1 where id = 1 and ticket_avail > 0

Alice will get the last ticket and bob will not get the last ticket because the where clause is reevaluated in update

Is serializable isolation really needed in this example?

0

There are 0 answers