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. Sa…
Tag: locking
waitlist in postgres, deadlock
I’m trying to create a waiting list in Postgres. Minimal code: status is important, because I want to be able to notify users when they are applied after waitlisted. But don’t want to notify them based on that they are in the first n position if they wasn’t waitlisted at all. Services are li…
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 …
How to queue up calls to stored procedures in Oracle?
I have a stored procedure in oracle (which schedules a one-time job to run another procedure, if this is relevant). The job calls another stored procedure which runs for a few minutes, and performs inserts updates and deletes and also uses loops. Now while the long procedure is running, if there is another ca…
MySQL: How to hold lock and make other threads wait for an insert that hasn’t happened yet
I’m confronted by a seemingly simple problem that has turned out to be pretty hard to figure out. We want to keep a record of every time we’re presented with a piece of marketing (a lead) so we don’t …
postgresql “idle in transaction” with all locks granted
A very simple delete (by key) on a small table (700 rows) every now and then stays “idle in transaction” for minutes (takes milliseconds usually) even though all the locks are marked as “granted”. What can I do to pinpoint what causes it? I’m using this select: which shows a lot …
Update Query with select and row lock
I got this query as part of a previous answer to my question. Now how to lock the rows in the select query to make sure no other thread over writes my update. Answer It’s not clear to me that you have the problem you seem to think you have. Updating a row in the table inherently places a row-level
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…