Re: Need Locking Edification
At 05:45 PM 8/19/97 EDT, you wrote:
}
}We're having a problem that when we are running deletes from a table,
}users are locked out from completing Selects, and Inserts fail. We
}can't quite figure out why this happens . . .
}
}Online v7.20.UC1, Unixware 2.1
}
}One set of errors (w/ Selects, after partial set of rows returned):
} -245: could not position within a file via an index
} -144: ISAM error: key value locked
}
}another (w/ Inserts):
} -271: Could not insert new row into the table
} (ISAM error text): Key value locked
}
}
}Here's a simplified table schema:
}
}create table "informix".calldata
} (
} calldate date,
} authcode char(10) <----- index on this field
} );
}
}create index "informix".ix_ac on "informix".calldata (authcode);
}
}The delete statement we run:
}
}delete from calldata where calldate between '08/11/97' and '08/17/97';
}
}
}At first we thought that the problem was that the program running
}inserts was trying to insert an authcode value (the field where there
}exists an index) that was being deleted. But when we checked our error
}log of rejected row inserts, we found that there were no rows where the
}authcode existed and calldate was "between 8/11 and 8/17".
}
}Another thing that happens is that the table's system table entries seem
}to be completely locked for the duration of the delete. Running
}`dbschema -d db_name -t calldata` elicits the following error:
}
} -252: Cannot get system information for table
} -113: ISAM error: the file is locked
}
}Why this would happen is a puzzle to me too.
}
}
}One possibilty regarding the Insert lockouts is that the table is in
}"page" locking mode. Perhaps when the delete locks rows (thus, locking
}keys) that it is deleting, it is also locking the index keys of other
}rows in the same index page, but which are not part of the deletion
}set. Does this make sense???
}
this makes a lot of sense to me. that's why when creating a table we
ensure that we set the lock mode to row.
Have you tried to alter the table to enable row locking instead of page
locking, and then see what happens?
}What puzzles me is that we are not deleting by the field that has an
}index (authcode). So what key value exactly is the error message
}referring to that is locked???
}
}Any suggestions/edification as to how to fix and/or work-around
}gratefully appreciated. Thanx.
}
}--
}* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
}ADDRESS ALTERED TO FOIL SPAMMERS: Remove "*NO-SPAM*" to reply.
}
}Cosmo Lee Multi-User Computer Systems Brooklyn, NY
}
} "JUST SAY 'NO' TO SPAM"
}* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
}
Best regards,
Nigel
+-------------------------------------------------------------+
|Name : Edmund Nigel Gall Tel: (868) 636 3153 |
|Title : Information Systems Specialist Fax: (868) 679 3770 |
|Company: Process Plant Services Limited |
|Address: Atlantic Avenue, Point Lisas Industrial Estate |
| Point Lisas, Couva, Trinidad & Tobago, W.I. |
+----- mailto:nigelg@ppsl.com ------ http://www.ppsl.com -----+