locking granularity is too big?...

HI Folks, IDS10 UC5 It would be very helpful if IDS could allow the following two transactions NOT blocking each other in some future Releases. T1: begin work; ...... update abc_test set dt=current where name='MD' ....... commit work; before T1 complete, we also started T2, but blocked by T1, T2: update abc_test set dt=current where name='CA'; The interesting thing is, this big locking granularity strategy even does NOT resolve ghost row problem! For example, it allows the following insert before T1 commit, insert into abc_test values(0,'MD',current); /* attach the test tables create table "informix".abc_test ( id serial not null , name char(10) not null , dt datetime year to second default current year to second, primary key (id,name) constraint "informix".abc_test_pk ); */ The behavior is confused about. Thanks, Frank

You've got SO MUCH going on here it's hard to know where to start. First, if you created the table with the default default locking mode, the locks are taken at the page level and if these are the only few rows in the table likely the first two are on the same page, so you got locked out. If you make the tables lock mode 'row' that problem is reduced. Second, if you begin each session with 'SET LOCK MODE TO WAIT 10;' the engine will wait ten seconds for transitory locks to be released before returning a lock timeout error (instead of a lock error). That will prevent instantaneous locks from causing spurious errors. Third, be sure to design your apps, especially interactive apps with optimistic locking (most easily implemented with a timestamp column and INSERT and UPDATE triggers). This will prevent interactive users from locking rows and index key nodes for long periods of time preventing other users from working. Fourth, IDS 11.10 has a new option to COMMITTED READ isolation: SET ISOLATION COMMITTED READ LAST COMMITTED;This permits a read-only user to see a consistent view of the data in a table without encountering any locks on updated rows. The user simply sees the version of the row that was committed as of the start of the transaction. Fifth, what makes you think that the INSERT should have been prevented of should have encoutered a lock? The row has a unique key, guaranteed by the SERIAL column (BTW you should have a UNIQUE index/constraint on that column), so there's no reason it cannot be added. The lock encountered by the second UPDATE is not a row lock or key lock that would prevent the insert. In order to improve multiuser performance IDS will actually insert rows to multiple pages at once so this new row added while the page containing the first two rows is likely being added to another page which itself is not locked. This is again a misunderstanding because of the effect of page level locking on the two updates you were attempting. Art S. Kagel ----- Original Message ----- From: Frank <ids@iiug.org> To: ids@iiug.org At: 9/20 13:14:07 HI Folks, IDS10 UC5 It would be very helpful if IDS could allow the following two transactions NOT blocking each other in some future Releases. T1: begin work; ....... update abc_test set dt=current where name='MD' ........ commit work; before T1 complete, we also started T2, but blocked by T1, T2: update abc_test set dt=current where name='CA'; The interesting thing is, this big locking granularity strategy even does NOT resolve ghost row problem! For example, it allows the following insert before T1 commit, insert into abc_test values(0,'MD',current); /* attach the test tables create table "informix".abc_test ( id serial not null , name char(10) not null , dt datetime year to second default current year to second, primary key (id,name) constraint "informix".abc_test_pk ); */ The behavior is confused about. Thanks, Frank ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.