In which mode should I lock my table?

Question

I have the table EMPLOYEE (idEmp,Name,lastName,Adress) and I want to block this table in a way that every time someone INSERTS a new employee, this new id must be consecutive to the last employee'sID...

-> Actually, there can be several people trying to INSERT a new employee AT THE SAME TIME, that's why I need a lock the table... these are the alternatives:

1. lock table EMPLOYEE in row share mode.

2. lock table EMPLOYEE in row exclusive mode.

3. lock table EMPLOYEE in share mode.

4. lock table EMPLOYEE in share row exclusive.

5. lock table EMPLOYEE in exclusive mode.

I can choose more than one.

Answer 1

While this looks like a trivial requirement, it really is not.

You may think that an auto-increment column or a sequence will provide a gap-less series but it won't: any rollback or error encountered during insertion will leave gaps in the column. Sequences can never be trusted to be gap-free.

In a multi-user environment, to be sure to get a gap-less column you need to serialize the process that gives the numbers. The two main ways to achieve that would be:

1. Serialize the inserts (In Oracle with a LOCK TABLE IN EXCLUSIVE MODE). As you would expect this will not scale well.

2. Postpone the actual numbering. You would insert the new rows with the desired column set to null. A follow-up separate process would then issue:

   UPDATE my_table
      SET gap_less_id = (SELECT MAX(gap_less_id) FROM my_table) + rownum
    WHERE gap_less_id IS NULL;
   

   You would need to make sure that this follow-up job is run only once at any one time (see for example another SO: Synchronisation of PL/SQL procedure. How to guaranty execution of procedure only one at time).

   This would of course mean that the value given to that column is delayed after commit, though it may be a small price to pay for the ability to scale.

My first advice would be to make really sure that the gap-free requirement is not bogus. Does it really matter if you are missing an id in your employee table? Probably not, this is just a primary key that is used to uniquely identify rows. You can use other tools to count the number of rows, or rank the rows by their inserted time. In that case use a sequence with CACHE 0, you should not get too many IDs missing.

If this is a real requirement such as a legal need, then ask yourself if you can use another column as a primary key. If you can accept that the numbering is post-poned until after commit, you can use the second method that will at least allow concurrent inserts.

Answer 2

What you are looking for is an "auto-increment" column for the id. In many databases, you can define such a column in the create table statement. Oracle does not make this so easy but it is possible.

The "book" solution would be to define a before insert trigger on the table. It would always look up the value of idEmp and then add 1. For performance reasons, you would define an index on the column.

Another solution is to use a sequence. This is described here.