Hi,

I am a newbie to Oracle and I would like to know how to use the dbms_lock package, so that I can implement table locking.

I use ODBC/ADO in VC++ 6.0 for accessing Oracle 8i. I am unable to lock the table in such a way that while the lock is active, no other user will be able to read or write to that table. My program will be running on several systems accessing the same database for read and write.

Any VC code or detailed step-by-step procedures will be greatly appreciated.

The DBMS_LOCK package is provided for locking non-database objects. To lock a table, use the LOCK TABLE command:

http://technet.oracle.com/docs/produ...4a.htm#2064407

Why do you need a table-level lock anyway? That's a pretty unusual thing to do in an Oracle application.

If I use the LOCK TABLE statement via the ODBC interface, it just does not seem to work when multiple users are updating the same table. The scenario is something like this:

1. User 1 retrieves the last record in a table and reads a number
2. User 1 adds 1 to the number and adds a new record to the table.

If between steps 1 and 2, another user performs the same operation it will result in two records having the same number in the field, which for my purposes, is an error.

How do I prevent this from happening? If user 1 executes "LOCK TABLE tablename IN EXCLUSIVE MODE;", user 2 is not prevented from reading the same table. I want to restrict this from happening.

Please remember that I am using VC and ODBC as my programming interface and not PL/SQL. What should I do?

The best way to avoid this error is to use a SEQUENCE to get the next number:

INSERT INTO t1 (id, ...) VALUES ( t1_seq.NEXTVAL, ... );

You create the sequence like this: CREATE SEQUENCE t1_seq [START WITH number];

This way, no locking (table or row level) is required at all.

The only "disadvantage" of this method is that you cannot guarantee that the ID values will be gap-free. But unless there is some legal requirement to have gap-free numbers, that really doesn't matter.

The way you are trying to do it would mean users spend most of their time waiting for locks to clear so they can insert a record, which is really unacceptable for most systems.

Hi,

Creating a sequence and using it to insert records into the table works like a charm! Thanks for the help. Couldn't reply earlier since the weekend came inbetween.

I have one more question. Is there any way in which I can find out what is the current value in the sequence? NEXTVAL increments the value by one or whatever the increment is, but I want the last value that was given by the sequence. Can you help me?

You can use CURRVAL, but note that that will only work if you have already used NEXTVAL in that session - it simply repeats the last value that YOU got for NEXTVAL (someone else may have used a higher value since, but you won't see that).