Hi,

I have a school project in which I have to use locking to handle multiple users. Is there a locking command to make Exclusive and Shared locks?

I'm totally in the dark about this. Any good tutorials you can point me to?

I'm using "Command Line Processor for DB2 SDK 8.2.2"

Thanks a lot!
Sincerely,
Arthur.

Yes, there is a locking command to make use of exclusive/shared locks. To lock a table you need to use -

LOCK TABLE <name> IN EXCLUSIVE MODE, similarly you can use SHARE for share mode.

the best documentation would be -

http://publib.boulder.ibm.com/infoce.../v8//index.jsp

You may want to look at isolation levels ... CS, RR, RS and UR. You can control the manner in which db2 locks the rows for a SELECT statement using these isolation levels ...

This is probably the tutorial you want to start with :
http://www-128.ibm.com/developerwork...11&S_CMP=LPDB2

Otherwise, locking is internally handled by DB2 ... You don't need to do locking using LOCK TABLE statements ..

The LOCK TABLE Statements are normally used when you want to avoid row locks escalationing to table lock - rather you issue LOCK at the very beginning ...

HTH

Sathyaram

DB2 is a “pessimistic locking” database in that it normally does all the locking for you based on the SQL statement that is run (as Sathyaram has said).

The isolation level determines how long read (share) locks are held and does not normally affect locks taken as a result of insert, update, delete, or select for update. However, a share lock on a row can prevent an update of that row by another application (until the share lock is released), so isolation level can be very important.

Locks are released when a commit or rollback is issued (explicitly or implicitly).

The biggest problem you may have in DB2 is to avoid lock waits or deadlocks that occur because of too much locking. Such event can be avoided by careful coding of SQL in the correct order, or the same order by multiple applications accessing the same tables.

Excessive lock waits or deadlocks can also occur if DB2 automatically escalates locks from row to page level. Do a search on lock escalation in the DB2 forum if you need more info.