4

How does one increment a field in a database such that even if if a thousands connections to the database try to increment it at once -- it will always be 1000 at the end (if started from zero).

I mean, so that no two connections increment the same number number resulting in a lost increment.

How do you synchronize and make sure the data is consistent? is there a must for a stores procedure for this? database "locking"? how is that done?

JasonGenX
  • 4,952
  • 27
  • 106
  • 198
  • 4
    `update the_table set the_column = the_column + 1` - they will wait in the order they issued the update statement until the previous update is committed. –  Jul 20 '15 at 21:09
  • Are you looking for a "gapless sequence"? Are you aware of the built-in sequence support but don't want to use it? – Craig Ringer Jul 22 '15 at 04:49

1 Answers1

2

What you're looking for is a Postgres SEQUENCE.

You call nextval('sequence_name') to get the next number in the sequence.

According to the docs, sequences are designed with concurrency in mind:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

EDIT:

If you're looking for a gapless sequence, in 2006 someone posted a solution to the PostgreSQL mailing list: http://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com. It appears there's also a lengthy discussion on locking, etc.

The gapless-sequence question was also asked on SO even though there was never an accepted answer: postgresql generate sequence with no gap

Community
  • 1
  • 1
Travis
  • 1,998
  • 1
  • 21
  • 36
  • 1
    Actually I suspect it isn't. If a connection rolls back before committing, the DB server is restarted, etc, there will be gaps. I suspect they're looking for a way to avoid this. – Craig Ringer Jul 22 '15 at 04:48
  • Hm. You may be right. See my edits above. If two connections increment the same number, consistency is lost and there is a gap. I was thinking consistency was the main concern, not the gap. – Travis Jul 22 '15 at 13:49