Phantom Read

Örnek

Bir toplantı salonuna rezervasyon yapmak isteyelim. İki kullanıcı da 

1. SELECT cümlesi kullanarak salonun boş olduğunu gördüler.

2. INSERT cümlesi ile salonu rezerve etmek istediler.

Açıklaması şöyle

There is a meeting room system that provides users to reserve a meeting room, and when the user has successfully reserved the meeting room, a new corresponding data will be added in the table as follows.

...

The problem occurs when two users want to occupy the same time slot in the same meeting room simultaneously, and they can both pass the first SELECT validation, so they can both insert a reservation, and a conflict occurs. And such a situation can not be solved by adding a lock, because there is no row to lock at the beginning.

Çözüm - Materialize Conflicts

Açıklaması şöyle. Burada time_slots isimli bir tabloya SELECT FOR UPDATE ile kilit atılır. Belirli bir saat arasını temsil eden satır kilitli olduğu için ikinci kullanıcı rezervasyon yapamaz.

To solve such phantom reads, the developer must use some tricks to reveal conflicts hidden under the same table.

One way is to create a new table and pre-fill it with data to act as a coordinator for simultaneous operations. In the case of this meeting room system, we can create a table time_slots that lists all time slots in advance as follows.

When the meeting room is to be reserved, we not only execute SELECT on the original booking, but also SELECT on time_slots, and we can add FOR UPDATE because the data already exists. It is worth noting that the new SELECT FOR UPDATE is executed before the original SELECT.

In that case, when the expected time slots of two simultaneous users overlap, they will be blocked by the exclusive lock and become one after the other, and the latter will fail directly because it sees the result of the previous completion.