Subject Re: [IBO] Pessimistic Lock
Author Helmut Steinberger
I used pessimistic locking because I don't like do let the user first
enter data, and then tell him that he cannot save the changes, because
someone other did it with the same record.
Then I discovered a strange problem using pessimistic locking. When I
lock a masterrecord with references to a detail table, i could not
insert new records to the detail table.
For Example:

table customers

custid integer,
name char (30),
custgroup integer,

table customergroups

custgroupid integer,
name char (20)

Then added the following constraint:
alter table customers add constraint cust_custgroupid foreign key
(custgroup) references customergroups (custgroupid)

Now if I lock a record of the customers table, I cannot insert any new
record into customergroups.

After discovering this behavior, I changed my whole application
(really big application with over 150 tables in the database) to non
pessimistic locking. Now the problem is gone.
I now do not lock a record, which is edited. If the record will be
saved, I check if the record was changed since the read and if so, I
reread the record, but let the fields, just changed, as they are.

For Example:
user1 reads the record
user2 reads the record

user 1 changes field1, field2 and field3 of the record.
user 2 changes field1, field4 and field5 of the record.

Then user1 saves and then after that user2 saves.
The record has now the changes of user1 in field2 und field3 and the
changes of user2 in field1, field4, and field5.

So it works very well.