Jeff, I think we’re running Postgres under the hood, here. Postgres uses Multi-Version Concurrency Control (MVCC). This is designed to provide the isolation necessary to support transactions, while allowing the database to scale to high levels of concurrency. In general, it works really well.
There are different levels of transaction isolation. The default level in Postgres is called Read Committed: Basically, the transaction sees the data that was committed before the transaction started. If another transaction changes the data you’re looking at before you finish, one of them will fail. The highest isolation level is Serializable, which guarantees certain other edge cases don’t occur, but increases the likelihood of conflicts. Per this link, Anvil provides Serializable isolation. You can read more about isolation in Postgres here.
Owen really is right–understanding how all this works can get ugly, and I couldn’t agree more that the best approach in the application is to keep it simple, and do one thing at a time. But in most cases it should be possible to reason through what the database is doing, provided you understand what the application is doing.
But what is the application really doing? Your front end is compiled into JavaScript, a non-blocking language. So it seems possible that the front end could trigger your back end routine two or more times in succession. Each of these may run as a separate request on the Anvil back end, resulting in concurrent attempts to update the database.
It also seems possible that one of your subroutines is opening a second transaction, and thereby causing a conflict with the caller, or a sibling. I don’t know for sure because I can’t see all the code.
Are either of these things actually happening? You can probably figure that out, but ultimately this brings us back to Owen: I think his solution (and mine) would be to ensure that they don’t. For example, I would not call this routine from an init function. I would call it from only one place–the button routine–and I would disable the button as soon as it’s pressed. On the back end, I would move all the database I/O into the main routine, where we can see it, and there’s no possibility a second transaction is opened by your own code. If you do those things, I’ll bet the problem goes away.
John