I need to generate and ID (a number sequence prefixed with a string) for a record from a c# application and store it in MYSQL, in turn when another record needs to be created the previously created records Id is fetched and it is incremented.
My question is, when the application is used by multiple users how can we guarantee that the same id will not be accessed at the same time. Moreover I assume we will need some locking mechanism, if so what is the best way to implement it.
Thanks,
You need some strategy to ensure that all client requests for "the next ID" are coordinated.
At the database level, this is usually handled by a sequence. For a discussion of simulating sequences in MySQL, see
MySQL equivalent of Oracle's SEQUENCE.NEXTVAL
The C# application would query the sequence in MySQL to get the next available number. Note that there is not a strict guarantee that all numbers will be assigned. For example, if a C# application gets the next sequence number but crashes, that number would be "handed out" but not end up in use.
If you do not want to allow the database to be the point of coordination, something else needs to be. Without knowing more about your architecture, it is hard to propose a better option.
If the ID can be very long and need not be sequential, just unique, you can use a GUID (which is effectively a 128 bit number).
