You are creating an application that allows organisations to manage employees. One of the tasks that it has to do is generate an employee ID when a new employee is being entered. One way of doing this is through this query:
SELECT max(empl_id)+1 FROM employee;
However, this query presents a problem in a multiuser environment: if more than one user is entering employee details at the same time, they will both get the same empl_id. To tide over this problem, one way to go is to look at the auto numbering solution provided by the database – however I personally find that solution limiting and have never used it.
The other approach is to create a single row table for global settings (in all probability your application will already have this) and maintain a field in that table as the last number used. Thereafter, the code can be written as below:
UPDATE settings_tbl SET lastnumber=lastnumber+1;
SELECT lastnumber FROM settings_tbl;
Remember that the order of the queries is important in a multiuser environment. Placing SELECT before the UPDATE can cause problems (locking has to happen first).
This piece of code should be executed at the time of saving the employee and not when a request for the blank employee form is generated. This is necessary so that one user of the application doesn’t have to wait for another to be finished. Note that the UPDATE lock is released only when you do the COMMIT or ROLLBACK.
Another way of doing the same thing in Oracle, one that I prefer myself and have used in a number of tight situations is the FOR UPDATE clause. This one allows you to do the SELECT first:
SELECT lastnumber FROM settings_tbl FOR UPDATE;
UPDATE settings_tbl SET lastnumber=lastnumber+1;
.