Can race condition occur in PHP / PostgreSQL (This is for an online queuing site)?

Question

I am creating an online queuing site. First, a user logs on the web app. On his first sign-in, he is assigned a queue number(which will be used to determine a user's order during an onsite event). Will a race condition occur? Expected users are between 600-1000.

I don't want any conflicts when the queue number of a user is being determined. I plan to assign a queue number by keeping track in the database of the last queue number given, and then increment that by 1 and assign that number to a User's "Queue_NO.".

Where user is an entity in the database.

Answer 1

I guess the best way would be to make a table called event_queue and generate numbers with auto increment.

id
id_event
id_user

can be the fields. When a new user signs in for an event, create a new entry to event queue and the id will be her queue number

Answer 2

The conflict might happen, indeed, but that is okay.

With transactional databases a data modification query might fail because of conflicts. The database will detect a conflicts and report it. (For example with a "could not serialize access due to concurrent update" error message). It is a common practice to check the status of the transaction and repeat it if a conflict happened.

Similarly, when you insert a new queue number into the database you must check if the transactional is sucessful and repeat it if it isn't.

You can create an autoincrement as Ali suggested but I often prefer the simpler "max + 1" logic.

Notice, however, that the autoincrement and the "max + 1" behave differently: if you delete a number of entries at the end of the queue (or if some insert transactions rollback), the "max + 1" approach will reuse their numbers. The autoincrement will leave these numbers unused.

P.S. An example: http://sqlfiddle.com/#!15/06b87/7

CREATE TABLE queue (id INTEGER NOT NULL PRIMARY KEY);
INSERT INTO queue SELECT COALESCE (MAX (id) + 1, 1) FROM queue RETURNING id;

(See also postgreSQL function for last inserted ID).