OCC Errors In Aurora DSQL: A Scenario Analysis
OCC in Amazon Aurora Database assumes concurrent transactions won't interfere & allows them to proceed without sync until commit time. Developers must code around OCC to handle potential conflicts.
Alice and Bob want to go to the cinema together and sit next to each other. However, each will book their seat. Will they be able to book two adjacent seats?
Let's run that in two Distributed SQL databases:
PostgreSQL-compatible behavior (YugabyteDB)
Optimistic Concurrency Control (Aurora DSQL)
I created the following table to book seats, identified by the row and seat number:
create table seats (
seat_row char, -- row of the seat in the theater
seat_number int, -- number of the seat in the row
booked_by text, -- null if seat is free, or the name if booked
primary key...