Re: Record Locking

To continue this theme, perhaps someone could explain the following feature of row locks. Using Informix-OnLine 4.10, and the SQL statement in question is SELECT ... FROM tablea WHERE col1 = $var1 ... FOR UPDATE OF col2 There are no indexes on tablea. tablea comprises 2 columns (col1 and col2), and 5 rows (col1 containing a,b,c,d,e), and uses row-level locking. There are two process running the above statement. However, we noticed that although one process was locking one row, it was preventing the statement being executed by the other process, even though the second process was accessing a different row. That is, more than one row appeared to be locked by the statement. Testing gave this result for all combinations of col1 values: ---- 2nd lock --- NUMBER OF ROWS LOCKED $var1 a b c d e a L L L L L 5 1 s b - L - - L 2 t c - L L L L 4 l o d - L - L L 3 c k e - - - - L 1 where L indicates that a row is locked (unavailable for the SELECT FOR UPDATE) and - indicates that it was available. Ordering the rows by the number of rows locked when that row was accessed: ---- 2nd lock --- NUMBER OF ROWS LOCKED $var1 a c d b e a L L L L L 5 1 s c - L L L L 4 t d - - L L L 3 l o b - - - L L 2 c k e - - - - L 1 When a SELECT * FROM tablea was run with no ORDER BY, the list of rows was produced in the same order, ie a,c,d,b,e - the physical sequence. So what seems to be happening is that when a row is locked, all following rows in the page (table?) are also locked. When an index is created on col1, we got the required result: ---- 2nd lock --- NUMBER OF ROWS LOCKED $var1 a b c d e a L - - - - 1 1 s b - L - - - 1 t c - - L - - 1 l o d - - - L - 1 c k e - - - - L 1 Is this a well-known phenomenon? Apologies if it is :-) Thanks in anticipation Keith