Concurrent editing of the same data

[steve-chavez]

Originally posted by @colinemondswieprecht in #219 (comment)

Most of the discussion so far has been about implementing multi-table data insertion or updating operations. I do agree that transactions are necessary in those cases, and I do agree that stored procedures, while helpful, can be annoying. But that's not what I want to talk about.

Not having transaction support in the client causes another nuisance: Supabase apps cannot use locks.

In many apps that have a central data repository (you know, a database), it is desirable to prevent concurrent editing of the same data. If you don't do this, you may end up with situations where two users open the same row for editing at the same time, and then user A saves, and then user B saves, overwriting A's changes.

So how do you prevent this? There's really two ways: pessimistic or optimistic.

In the pessimistic approach, you would lock the row when you start editing:

begin transaction;
select * from some_table where id = $ID for update;

Then, when the user is done editing, you just do

update some_table set foo = $SOMETHING where id = $ID;
commit;

This doesn't work in Supabase because you can't hold the transaction open. Running select ... for update, even using RPC, doesn't do anything, because Supabase will always auto-commit (or auto-rollback) every transaction after the command is run, releasing the lock.

There is a possible workaround. What does work is having a separate table some_table_row_locks and maintaining reified locks in there. But this is not compatible with Postgres's native locking system, and now every operation that runs on the backend also has to be aware of this additional table of locks, lest backend operations update data that is currently being edited.

In the optimistic approach, you assume that conflicts usually don't happen, and just fail if the data has been edited from under you. When the user opens the row for editing, remember the current version.

public async beginEditing(rowId) {
    this.rowId = rowId;
    this.rowVersion = await this.supabase.from('some_table').select('updated_at').eq('id', row_id);
    ...
}

Then, when the user submits, download the current version again, check if it's equal to the version you previously saw, and only if it is, then you perform the update.

public async submit() {
    const currentVersion = await this.supabase.from('some_table').select('updated_at').eq('id', this.row_id);
    if (currentVersion !== this.rowVersion) {
        alert('Cannot save, please reload page!');
        return;
    }
    await this.supabase.from('some_table').update({ name: this.name }).eq('id', this.row_id);
}

This doesn't work in Supabase either because without transactions, there is a race condition between the version check and the update: if another user submits after we downloaded currentVersion, but before we finished calling update(), then we will silently overwrite the other user's changes.

Again, a workaround is available: don't ever call update(), instead, do all updates using stored procedures and rpc(). Then each stored procedure that performs an update has to take the last seen row version as an additional parameter, and has to perform the check as above. Again, this does work, but is an awkward hassle.

Obviously, the pessimistic approach as outlined above has severe scaling problems, and I would understand if that was the reason that the team is hesitant to implement this. However, I'm not certain if maximum "web-scaleness" is what most of Supabase's customers want anyway? Although I suppose it's a pointless discussion, because that's not even a client-side issue: even Supabase's backend doesn't support this, because the load balancer just can't do this.

[steve-chavez]

There have been two related discussions on the postgREST repo:

• Concurrency control in multi-user environments PostgREST/postgrest#1069
• Support for standard Idempotency-Key header PostgREST/postgrest#2998