educative.io

Educative

Can we utilize Repeatable Read tx isolation level?

Hi!

In this chapter, we are told to use Serializable tx isolation level to keep seats 54, 55 and 56 isolated from other users that are trying simultaneously to book them.

But why do we exactly need Serializable?

From my point of view, we wouldn’t face Phantoms because SeatID is a unique field and there won’t be another row with SeatID = 54, hence we don’t need to lock a range but we simply need to lock only these particular rows with SeatID = 54, 55, 56.

Consequently, we can use Repeatable Read tx isolation level in order to prevent Nonrepeatable reads.

Hi Pavel_Polubentcev,

This is Umair from educative. Thank you for reaching out! We are happy to hear from you.

We recently received a message from you on the course “Grokking the System Design Interview(Design Gurus)”. We’ll be looking at your issue and let you know when this is fixed.

If you have any further queries, please let us know.

Best Regards,
Umair Cheema Developer Advocate

I also suspect we better use pessimistic locking

Thank you for your suggestion, Pavel. We’ll surely consider that and let you know if this is more appropriate.

Best Regards,
Team Educative.io

Did not get the response yet @Design_Gurus

Yes, if we can guarantee that no new rows will be inserted in-between the rows we are selecting, we can use Repeatable Read isolation level. Following explains this in details: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-ver15

I believe that Repeatable Read is not enough, since a client can create/delete a row in ShowSeat table. We need Serializable here.

Hi @Pavel_Polubentcev, I was about to post that with Read committed will be enough when I saw your post.

For me, if we put in the update Show_Seat ... SQL sentence, a clause where Status=0 it is gonna lock concurrent updates, so we only will proceed with booking update if seats update succeed. This way I think we can avoid overhead of Repeatable Read.

Please can you give me some insights why we could need Repeatable Read. Thanks!

For a Web service like TicketMaster, there must be a admin page so that administrator can prepare new Shows and new ShowSeat before that show is published to the internet.

Therefore, I believe it’s POSSIBLE to prepare all rows of ShowSeat by pre-install all the status of each new ShowSeat to an initial value (say 'Available (0)').