Locks on records 
Author Message
 Locks on records

(DB2/400 V4R4)

Hi,

I'm trying to have this behavior:

(S means SELECT order, SF means SELECT FOR UDPATE
orders are made on the same records,
NOK means second transaction is wainting until
the first one committed)

   Transaction 1   Transaction 2
      S                  S            OK
     SFU                 S            OK
      S                 SFU           OK
     SFU                SFU          NOK

In brief, I would like to authorize 2 applications
to read same data only if one (or none) want to
modify them. I do not authorize a second application
to read data for update if the first one take
the data to modify them (transaction is waiting
until commit).

On Oracle 7.3.3, READ_COMMITTED isolation level
provides this behavior.

On DB2/400 (V4R4), we have the following behaviors:

   T1   T2    *CS   *RR
   S    S      OK    OK
   S   SFU     OK    NOK
  SFU   S      OK    NOK
  SFU  SFU     OK    NOK

Is this normal ? How can I obtain the asked behavior ?

Thanks a lot.

--
-------------------------------------------------

Sent via Deja.com http://www.***.com/
Before you buy.



Fri, 07 Mar 2003 03:00:00 GMT
 Locks on records



Quote:
> (DB2/400 V4R4)

> On DB2/400 (V4R4), we have the following behaviors:

>    T1   T2    *CS   *RR
>    S    S      OK    OK
>    S   SFU     OK    NOK
>   SFU   S      OK    NOK
>   SFU  SFU     OK    NOK

> Is this normal ? How can I obtain the asked behavior ?

The behaviour you got is normal for DB2.  Normally the best approach is
to always use a cursor with SELECT ... FOR UPDATE when you want to
change data, and bind for CS or even UR.  DB2 will allow both
applictions to obtain an 'intent update' lock on the same record, and
for other applictions to read it.  If one of the applications actually
performs an UPDATE WHERE CURRENT OF then the lock will be promoted to
update.  This will prevent the other applications from updating the
record.

I think this will give you the behaviour you want, but not in the way
that you want it.

The currency model for DB2 is very different from that in Oracle, which
gives a lot of differences in behaviour.  Some people do not like some
of the effects of these changes, but others do.  The DB2 model has been
proven to allow a far higher level of mixed update and query to run at
the same time than the Oracle model allows, so I like most of it.
--
All information given is a personal opinion.
You are responsible for any changes to your environment.

Sent via Deja.com http://www.deja.com/
Before you buy.



Fri, 07 Mar 2003 03:00:00 GMT
 Locks on records
[snip]

Quote:
> I think this will give you the behaviour you want, but not in the way
> that you want it.

> The currency model for DB2 is very different from that in Oracle,
which
> gives a lot of differences in behaviour.  Some people do not like some
> of the effects of these changes, but others do.  The DB2 model has
been
> proven to allow a far higher level of mixed update and query to run at
> the same time than the Oracle model allows, so I like most of it.

[snip]

Well, this is the correct behavior ... for DB2.

An alternative could be optimistic locking. Do you know another one ?

Thanks.

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 08 Mar 2003 14:09:33 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Locking/Locked records

2. ADO and a counter in a record ->lock a record with mulituser

3. How to force ADO to lock current record, without changing record's contents

4. How to lock a record in SQL Server?

5. Locking a record on an MS-SQL Server

6. locking the records

7. question on locking rows/records in SQL Server (from a VFP application)

8. Row locking using record sets....?

9. System locks when record change attempted.

10. Programmatically Lock a Record

11. How to lock a record?

12. Locking a record for update


 
Powered by phpBB® Forum Software