I am working on a helpdesk ticketing system. When a user submits a ticket I want to assign the ticket a unique number, for example: 20190724001 where the first four digits is the year, next two are the month, next two the day and the last three would be the next consecutive number FOR THAT DAY and then the numbering would start back at 1 the next day. I know to query the table for the next number where the date equals today, but I do I obtain the next consecutive number without another user grabbing the number at the same time? Odds are slim, but I want to plan for the worst.
Don't confuse ticket numbers with id numbers. While, technically, you could use your ticket number as your primary key I wouldn't. So, when you save a new record, ticket number (char 11?) would be a stored procedure that gets the highest ticket number that is LIKE '20190724%' adds 1 and returns it back as a string. Alternatively, ticket number could be 2 columns - one for the date, one for the day's counter and when someone searches on the ticket number your script breaks them apart and searches on the two bits.
Wouldn't you be better off using an numeric auto-number field? If you're basing the primary key on the date, what happens if the current server date and time get changed to an earlier time for whatever reason, you risk duplicate ticket numbers, unless you use transactions. What about during the twice yearly change to/from daylight savings time, you could end up with it looking like an entry was submitted on one day but in reality it might have been submitted either the day before or the day after. For usability you might want to have it so that the user types in the ticket number with the digits in groups (eg 1234-5678-9012) and then when the ticket number is submitted to the server, have the server strip out the - before doing something with the ticket number. If the date is important then you should use UTC and then to the user either display all times in UTC or display the times with whatever off-set is appropriate for their timezone. btw don't forget that some countries write dates with the month then the day and others with the day then the month
I am assuming that you have a separate table for tracking which numbers have been used or not. If so, there are two ways to prevent duplicate numbers. 1) If you can LOCK the table, then LOCK the table, update table with next number which you also grab, then UNLOCK the table. During the time the table is LOCKed, no one else can grab a number. 2) If you cannot LOCK the table, then add the next number. Check for duplicate number(s) added at the same time. If no duplicate, then grab your number. If there are duplicates, delete only the excessive duplicates and try again until you get your unique number. Just make SURE you do not delete ALL numbers that were duplicates as ONE of them someone else has grabbed for their unique number.