Re: fetching unique pins in a high-transaction environment...

On Sun, Oct 29, 2006 at 08:32:12AM -0800, Bobus wrote:
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 unique pins, while eliminating
> any waiting?

What are you doing that holds locks for so long? If you do a select for
update, take the first row, update and commit, you should be able to
handle dozens of those per second.

In any case, another approach I've seen is to divide the list into
several. For example, make your query do a:

select for update <blah> where pin > 'X'

where X is a random number between 0 and 9. That cuts the amount of
contention dramatically, so you can use the simple method.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.