how to catch db error

A better way would be to create a separate table for holding the last id's.

table last_id
blue_id
yellow_id
green_id

When you create the users record assign the correct id with an increment method.

still cannot, because data entry by many people / user

it could be red inputted simultaneously by several users

So fundamentally, you have a race condition.

The cleanest way to solve this is to use the primary key to store the row in the database, the use a lock to fix up within the transaction the colored tags.

We do this when creating invoice numbers, create a second table with the last number in, lock it as part of the transaction and then add a 1 to the stored value. Then at the end of the transaction, update the value in the companion table.

This way, you can block other accesses to the critical value, giving you access. when the transaction completes, it is unlocked and the next one can proceed.


Be warned, this will not scale to large numbers of users, as at some point, the queued transactions will begin to time out.