RE: Issues with row level locking. [903]

Keith, I think the actual reason for the lock is different. The problem is that most probably the server is using sequential scan to search the proper record for the second update (just because the table is extremely small). To make that sequential scan, the server must first read the first, locked row of the table. With the default 'committed read' isolation level, the second session is unable to read that row. It must wait until the first session releases the lock by commiting the transaction. The workarounds are: - Explicitly specify --AVOID_FULL optimizer hint to make the server use index search instead of sequential scan in the 'update' statement; - For large tables, do 'update statistics' regularly - Use 'dirty read' isolation level in the second session ------------------------------------------ Alexey Sonkin > -----Original Message----- > From: Simmons, Keith [mailto:keith.simmons@office2office.biz] > Sent: Tuesday, April 06, 2004 6:08 AM > To: classics@iiug.org > Subject: RE: Issues with row level locking. [903] > > Glen > > The problem you are encountering isn't particularly related to the lock > mode > of the table, rather to the fact that when an index entry is being updated > the entries either side of it are also locked. In youu limited example of > two records, updating one will lock both entries on the implicit index > created by the primary key constraint. If you did not have the PK, or had > more records and were updating two that were more than one entry apart you > would not encounter this problem. > > Keith > > -> -----Original Message----- > -> From: GLEN BAYLISS [mailto:glen@hunter-systems.com.au] > -> Sent: Tuesday, April 06, 2004 9:35 AM > -> To: classics@iiug.org > -> Subject: Issues with row level locking. [902] > -> > -> > -> Hi, > -> > -> I am experiencing some problems with row level locking, > -> which I'm sure is due to my mis-understanding of how it > -> should behave. > -> > -> I have a simple table defined in a buffered database with > -> lock mode set to row. > -> This is on IDS 9.40.UC2E1 running under Fedora. > -> > -> The definition is: > -> create table test_locks ( > -> col1 integer, > -> col2 integer, > -> primary key (col1)) lock mode row; > -> > -> I insert data into the tables as follows: > -> insert into test_locks values (1,100); > -> insert into test_locks values (2,200); > -> > -> In DBACCESS session 1, I execute the following: > -> begin work; > -> update test_locks set col2 = 101 where col1 = 1; > -> > -> In DBACCESS session 2, I execute the following: > -> begin work; > -> update test_locks set col2 = 202 where col1 = 2; > -> > -> On execution of this, I receive an error (-244) indicating > -> that the record is locked. > -> > -> I realise I can overcome this by setting Lock mode to wait. > -> However, I would have thought that because I'm trying to > -> update records with different primary keys, this should have been OK. > -> > -> Can someone please explain what I am missing? > -> > -> Any help appreciated. > -> > -> Cheers > -> Glen > -> > -> > -> > -> > > > ************************************************************************** > ******** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be > disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of > any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this > message > is free from such problems. > ************************************************************************** > ******** sending to informix-list sending to informix-list sending to informix-list sending to informix-list