I am implementing a simple pessimistic locking mechanism using Postgres as a medium. The goal is that multiple instances of an application can simultaneously acquire locks on distinct sets of users. The app instances are not trying to lock specific users. Instead they will take any user locks they can get. Say, for example, we have three instances of the
Tag: concurrency
How to select a row, lock it, update it, then select again?
I have a table with these 3 columns: task (string) status (string) date (datetime) I want to write a query that does the following: Selects the first row WHERE status != “In-Progress” Sorted by Date (oldest first), and Locks it – so other computers running this query concurrently can’t read it. Updates the Status column so status = “In-Progress”. Return
Why doesn’t SQLite appear to lock my table / row?
I’m tinkering with an in-memory sqlite database, trying to wrap my head around concurrency. Here’s an example using SQLAlchemy OUTPUT Here, I use two connections to write data to the same record. I would expect the output to be “A112”, because I thought the first connection would have a lock on the record until it COMMITs, but to my surprise
Is it possible to lock on a value of a column in SQL Server?
I have a table that looks like that: Id GroupId 1 G1 2 G1 3 G2 4 G2 5 G2 It should at any time be possible to read all of the rows (committed only). When there will be an …
Race Condition between SELECT and INSERT for multiple columns
Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4 If we want to return multiple columns now …
Is there a way to execute an SQL merge atomically?
I am looking for a way to connect a tuple of values with a random UUID in a non-locking manner and without the potential to fail a transaction due to concurrency constraints. The table I need to …
Only inserting a row if it’s not already there
I had always used something similar to the following to achieve it: …but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic? The problem is that this is almost impossible to recreate at will. Perhaps I could change