0

This is a more logical problem rather than SQL, but I hope you will be able to help me.

I have a list of wallets in my database, something like this:

wallet_id SERIAL PRIMARY KEY | balance BIGINT

I am getting requests from clients, to register their wallets in the database, there is a registration fee for that, there is a third party that decides what the fee will be based on the wallet_id, I don't know what logic they use and can't predict the fee. I do however have to make sure that the client has sufficient balance to register a wallet. I also need to make sure that all their wallets ids are serial. I don't have a goal to register as many clients as possible, I simply need to respond to each client request for registering if the registration succeeded or not and what was the fee the client was charged with.

And to add on top of that I got multiple processes that communicate with the database and trying to register clients. So my question is how can I synchronize those processes in order to succeed in my task?

I thought about a solution where I lock the entire table, register the wallet in a transaction, determinate the registration fees and if there is an insufficient balance I roll back. But I don't like the idea of locking the entire table, I would prefer to somehow use a temporary table or something else... Any ideas?

Ilya Gazman
  • 31,250
  • 24
  • 137
  • 216
  • Any *real* problem with locking a whole table? Given you need a guaranteed total order - you still need to lock something. Alternatively you can have a unique index set for the wallet_id, and insert a new row with `MAX(wallet_id) + 1` value, and retry until you succeed. – zerkms Feb 18 '18 at 01:10
  • I simply don't understand the problem. What is your concern about a `serial` table? – Gordon Linoff Feb 18 '18 at 01:27
  • @GordonLinoff `serial` column does not guarantee absolutely ordered contiguous series of values. – zerkms Feb 18 '18 at 01:36
  • 2
    A serial column ensures monotonicity. It does not preclude gaps, but I really don't understand the issue about any possible gaps. – Gordon Linoff Feb 18 '18 at 01:36
  • 1
    @GordonLinoff from what I see - the requirement of a gapless series comes from a 3rd party software that is out of the OP's control. So it's a business requirement to have a gapless values. – zerkms Feb 18 '18 at 01:37
  • @GordonLinoff [how do you prevent those gapes then](https://stackoverflow.com/questions/48847785/how-to-preclude-gaps-in-postgresql)? – Ilya Gazman Feb 18 '18 at 02:07
  • Why did you ask the same question twice? – zerkms Feb 18 '18 at 02:11
  • I have already suggested 2 possible solutions - a lock and a unique index – zerkms Feb 18 '18 at 02:11
  • @zerkms the other question is a step in solving this question, but in this question, I have a very specific logical problem that is completely different from my other question. – Ilya Gazman Feb 18 '18 at 02:23
  • @Ilya_Gazman the answers I provided here can be applied to the other question as well: a lock or a unique index + `MAX(wallet_id) + 1` and retry. – zerkms Feb 18 '18 at 02:43

0 Answers0