I have a project that I'm developing written in VB.NET 2005 against a SQL Server 2005 Express database. There may be up to 25 users accessing the database at the same time – very unlikely, but possible.
The software is for issuing municipal burning permits for local residents. Each permit must have a unique PermitNo that I can calculate from accessing the last record in the tblPermits table. The PermitID is nothing special; it’s just the last two digits of the current year, followed by the number of permits issued.
For example, if the last permit issued had a permit ID of 10-3042 that would indicate the permit was issued in the year 2010, and was the 3,042 permit issued that year. The next permit issued would be 10-3043. It’s no problem calculating this in vb.net code - using query's against the SQL server.
My question is how I should handle if two of the 25 concurrent users want to issue a burn permit at the exact same time? Very unlikely I know, but how can I prevent conflicts? The PermitNo field is indexed but should have no duplicates.