0

I have many places in my application where users are doing something that should be non-concurrent in per-user-and-namespace bases. So that means that:

  • Two users can press the same button at the same time
  • One user can't press the same button second time until the first press is finished
  • One user can press different buttons at the same time

What solutions I tried:

  1. Advisory lock with hash function on user uuid - has collisions and this advisory lock, but the lock locks all user interactions - so he can't press two buttons at the same time
  2. INSERT & SELECT FOR UPDATE - good, but I need to prepopulate table with "pending" row so it pollutes table schema with unnecessary columns.
  3. Want to implement: locktable. So what I mean is to create a table with advisory-like locks. It will have two columns namespace and id - composite primary key. Each time user presses button#1 there will be INSERT&FOR UPDATE for namespace=button#1 and id={userid}.

I feel like there are some hidden stones of this (3rd) approach - do anyone knows about it? Could you kindly list these problems?

I'm sure it will be worse in terms of performance than advisory locks, but is it much-much worse? Also, I couldn't really find implementations of this approach - looks like I came up with a stupid idea that no one use for some important reason.

Check this interesting conversation in 2009 regarding this problem: https://www.postgresql.org/message-id/[email protected]

By INSERT&FOR UPDATE I mean:

INSERT INTO adv_locks VALUES ('btn1', '123') ON CONFLICT ON CONSTRAINT adv_locks_pk DO NOTHING;
SELECT 1 FROM adv_locks WHERE namespace='btn1' and id='123' FOR UPDATE;

So that way there is always a row in a table with this table that can be locked. (I don't care about table size much yet)

I understand that an order of this could be broken (I mean request#1 creates a row and then request#2 selects the row faster), but it isn't a problem for me.

Improve this question
6
  • Wouldn't that be better solved using synchronization constructs inside the application? Commented Mar 1, 2024 at 12:03
  • I had a similar approach some time ago, and from my experience it is a very convenient idea to develop but from the database side it is a single point of contention as all sessions need to go there and update it. so in my solution I disabled the lock scalation for that table and all the updates and were done with (rowlock) still that was not a good solution if you have lots of sessions and transactions. I dont put this as an answer as my case was sql server but I thought I would help you out. Commented Mar 1, 2024 at 15:42
  • @LaurenzAlbe what would you suggest to google? First thing I thought about is another service/db that has advanced locks or notification patterns. But we have db, that logically should cover this, since I'm preventing the same data to be inserted twice (it's not just a row, usually it's bunch of updates like balance, transactions, and special action rows - so logically fits very well into db responsibility) Commented Mar 2, 2024 at 17:24
  • @MarcelloMiorelli yeah, also thought about it. Seems like it will be a bottleneck if all functionality of website will be inserting new lock-rows - the worst scenario. Otherwise updates on existing locks should be fine, since they're not locking each other. Thought primary key index must be veeery large after some time... Idk, thanks for the response. Would be excellent to hear someone who gone thought many solutions and chose some not that obvious one, but very good Commented Mar 2, 2024 at 17:27
  • You mean that you have more than one application server, right? Commented Mar 4, 2024 at 3:30

1 Answer 1

Reset to default
2

The idea to synchronize application threads via regular row locks in a special table is a bad one. The reason is that you need to keep a database transaction open as long as you want to hold the lock, which can take quite long, given that we are talking about user interactions. Long running transactions are a bad thing, because they prevent the progress of the important cleanup work from VACUUM, and you will end up with bloated tables and perhaps transaction ID wraparound problems.

If you use advisory locks, which don't require you to keep a transaction open, your lock table can avoid that problem. But advisory locks are tied to database sessions, and an application thread would need to hold on to a database session while it waits for the user to finish pressing the button. That will cause a problem with connection pooling, and you want to have a connection pool in transaction mode if you want your application to perform well.

You might think that my concerns are ridiculous if the time span for the lock is as short as the push of a button. True, that might normally be a short time, but you have to envision problems like a crash of the application or a network problem between the push and the release of a button.

I would go with a solution that inserts or updates a row into a table whenever a button is pushed and released. If you use a timestamp, you can also use that to time out a "lock". if you use HOT updates, that can work well.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.