I need a locking mechanism for a table row to ensure that at most one of multiple potential concurrent serverless functions hitting supabase will win and then perform some external work that eventually results in a database update before any other serverless function modifies therow. I can think of several ways to do this with more traditional Postgres clients; what is the recommended approach with supabase? ...or is this cutting against the grain? I need the equivalent of a select_for_update atomic client-side transaction. I cannot put this in a stored procedure because the client-side transaction might fail and need rollback.

Hello @VΞLO! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the

/title

command! We have solved your problem? Click the button below to archive it.

Transactions work at the db connection level. So keeping one open across a HTTP api is going to be tough and goes against the REST principle of statelessness. You can achieve rollback-like functionality with supabase like,

try:
    // invoke stored_procedure
    // run other actions that could fail
  catch:
    // invoke rollback_stored_procedure

But this won't provide a way to deal with race conditions. Maybe you can toggle a flag within a stored procedure after obtaining a access exclusive lock, so that only the serverless function that manages to lock and toggle the flag continues the rest of the processing.

VΞLO (2022-03-21)

I may have to conclude that it cannot be done since there is no guarantee that the serverless function has not halted after acquiring the lock and before finishing what work is to be done. That would cause a deadlock situation and/or possibility of duplicate work which, for example in financial apps would lead to occasional double spending.

yeah, it's not a perfect transactional system.

So I think there's a good compromise here. I can write an agent process that does all the hard stuff and connects directly to POstgres and still have serverless functions closest to the front-end. Anything else I wil have to write stored procedures.