Hi,

I have written a DB2 stored procedure which updates the values of a certain column in the database currently my stored procedure has the followin query.

UPDATE
Table
SET
column_nm= IN_NEW_FIELD_VALUE
WHERE
column_nm = IN_OLD_FIELD_VALUE;


Now I need to update my stored procedure so that I can obtain a lock on the table so that no one else can access the table when it is getting updated.

Please suggest me a way how can I obtain a table level lock.


Thanks
Arun

there is a lock table command: see
http://publib.boulder.ibm.com/infoce...c/r0000972.htm

Different question: why exactly do you want to lock the table? I'm asking because the DBMS will automatically lock whatever needs to be locked. Maybe you are searching for a solution for a non-existing problem...

Hi,

I am also under the impression that the tables will be implicitely locked by the DBMS but the thing is that I am performing update in more than one table in a single stored procedure call.

So my question is whether DBMS will have the lock on the table which is being updated or it will have the lock on all the tables in the SP unless the execution of SP completes.

If it is having the lock only at the table being updated then I will have to obtain the lock explicitely as I want my stored procedure to execute as a single atomic transaction.

Please suggest do I realy need to have a lock. If yes, how?

Thanks
Arun

By default, DB2 for Linux, UNIX, Windows locks at the row level. When you do an update, insert, or delete, the row is locked in exclusive mode (X). Locks are not released until you issue a commit (or when the calling program issues a commit). Therefore, all your updates will be in a single unit or work (or single atomic transaction) until you commit.

Hi

Thanks for such a quick reply.

I just need to understand if implicit locking is being done by DBMS itself, then in which case do we need to lock the table explicitely and how can we do so?

Thanks
Arun

It is unlikely that you need to lock the entire table. But if you do, see the LOCK TABLE statement in the SQL Reference Vol 2.

That's actually a basic principle of database systems to prevent "lost update", "read uncommitted", "non-repeatable read", and "phantom" problems. (If you don't know what I mean, have a look at some good standard database book or search a bit on the web.) There are different techniques to prevent the mentioned problems. Also read up on "isolation levels" because that is the mechanism to influence which of those issues you can live with in your application in order to get better concurrency.

Now, DB2 will lock everything that you access or modify in your database - regardless of the table in which the respective data resides.

The only reason where you want to lock a table explicitly is when you update nearly the complete table and want to avoid lock escalations. If you don't know what lock escalations are, you will not need the LOCK TABLE statement.