does anybody know if the built-in 'Action Type' for insert/update on the Retool Database queries uses specific mutexes for concurrency? like does selecting 'Update an existing record' use FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE or does it just use the normal SELECT? I'm trying to figure out if I need to go back and switch all my Retool DB queries that use the GUI over to SQL Mode to take advantage of PSQL row-level locks.
use case: chat client that uses polling on a shared table/row to get and make updates to the chat history. I use 2 tables, 1 for the message data and 1 for the conversation data that holds a list of message_id's in a JSONB column named history
problem:
- race-condition prevention.
- I was looking to use
FOR NO KEY UPDATEorFOR KEY SHAREwhen polling/reading the history andFOR UPDATEwhen adding new messages to the history list. - I don't want to implement mutexes if they're already being used with either the built-in PSQL mutex/locks or some custom handler used in the background of the Retool framework.... duplicate mutex usage can cause other problems that snowball and aren't as noticeable until it's a serious problem for everybody
despite loving data structures I'm not great when it comes to databases (especially relational ones), so I might easily be overthinking this. I also wouldn't put it past me to be looking at using the wrong row-level mutex/lock for PSQL, which is why I was considering just adding a column to use as my mutex value but that introduces extra read/writes
