Hi guys
I am very much new to DB2

I am having a problem for getting lock on records in DB2 V5R2 UDB on our AS/400 (iSeries)

How to get a lock on ROW (not a table) for a set of transactions so that

1. Other users should be able to update other records (on which lock is not there)

2. Other users should not be able to see un-committed records

3. Other users should be able to select committed records including record which is locked.

4. Other users should NOT go into wait state if their select statement includes a record updated by any other user.
Rather he get the last committed record.

I have tried many permutation and combination for the same with Isolation levels and RS and CS but all in vain

if any one can help me out..

Can you also tell me how exactly
Select * from <table name > where <condition> with RS / CS works internally.

Does it internally commits the records in the file till it is committed at transaction level / rollbacked. please do send in depth information regarding this too.

Thanks in advance

Regards
Manav Khanna

DB2 doesn't let you do this.

Oracle will allow you to select the "before update" version of the row as it uses rollback segments/redo tablespaces in order to store this information. DB2 doesn't have this feature, so you can either:-

1. Implement your own version of rollback segments using triggers/tables
or
2. Use select .... with ur to read the uncommited version of the row
or
3. wait for the user updating the row to commit.

So long as you ensure that you code only short transactions and use appropriate indexes, locking doesn't tend to be as big an issue as Oracle people often imply.

Our DB2 UDB 8.1 (AIX) database is handling 1m web-based transactions a day/ 30 concurrent transactions without any major locking issues.

jdey@macehill.co.uk

DB2 stores the locks in the locklist memory (Oracle stores the locks on each data page, and sometimes has to extend the page if it needs more room).

Isolation level affects how long share locks are held (usually the result of a select statement). For RR, the share locks are released at commit time. For RS, the share locks are released when the SQL statement has finished.

As already mentioned by jdey123, with update locks DB2 will not allow a transaction to see the before image if a row is currently locked by an un-committed update by a different application thread. The application thread will wait (assuming it wants that row) until the lock is released (which occurs when the first application commits the work).

Thanks for your reply guys,
theres one more case with RS

If a user say User_1 in Read Committed mode

1. Selects a record WITH RS
2. Update the same record

Now if another user say User_2 also in read committed mode is able to see
this new updated record WHY??
3. Select the same updated record.
it give me the result instead of going into WAIT....

There no commit invloved till now

Regards
Manav Khanna

Are you sure you turned Autocommit off in user_1's session ... Once you do user1's tasks, check the snapshot to confirm that the locks are held in the database ... Then try user_2's part ....

Cheers
Sathyaram

Yeah Auto commit is off
as i am trying the same through my 2 - java application running on my system.

where auto commit is set to false as
i tried to rollback the transaction and it did. The rollbacked result is reflected on both the user's end. If auto commit was not set to false then it could'nt have happened..

Regards
Manav Khanna