Technique for locking needed with SE

Path: emory!swrinde!cs.utexas.edu!uunet!psinntp!uupsi!gdc!nms!bergquis From: bergquis@nms.gdc.com (Brett Bergquist) Newsgroups: comp.databases.informix Message-ID: <410@wimpy.nms.gdc.com> Date: 31 Oct 91 12:59:33 GMT Organization: General DataComm, Middlebury CT I have a logical entity stored in a database using Informix SE. I am using ESQL/C as the front end to SE, and the database is using transactions. This entity is composed of may parts that are in separate tables. Some of the these parts exhibit a many to one relationship with the base part of the entity. Entity Main Table +----------+----------+-------------------+ +---|part1 key |part2 key |rest of entity data| | |part1 key |part2 key |rest of entity data| | |part1 key |part2 key |rest of entity data| | +----------+----------+-------------------+ | Part 1 Table | +----------+----------+ +-->|part1 key |part1 data| +-->|part1 key |part1 data| +-->|part1 key |part1 data| | +----------+----------+ | Part 2 Table | +----------+----------+ +-->|part2 key |part2 data| +-->|part2 key |part2 data| +-->|part2 key |part2 data| +----------+----------+ A user may view or modify the entity. Viewing is a read only operation. Modifying the entity requires that the entity be locked until the modification is complete. I would prefer that the entity be locked when the descision to modify it is made, in order to prevent multiple users from modifying the same entity at the same time. The problem that I am having is how is this done using informix ESQL/C and the SE. What I would like to do is to lock the main entity row and each part1 and part2 rows that relate to the entity. There is no explicit row level locking capability with SE. I know that one can be simulated by declaring a cursor for update for the main entity and fetching it, and by declaring separate cursors for the part1 and part2 rows that relate to the entity, fetching and updating where current each row. Within a transaction, this will have the affect of leaving the desired rows locked. Because of the posibility of the large number of parts that may be associated with one entity, I may run out of locks however. Locking the individual tables does not appear to be a solution because the modification is performed by a user at human speed. This would lockout other users modifying other entities for a long period of time. I suppose that I could employ a locking protocol which dictates that to perform any modification to an entity requires that the base entity row be locked. This should work as long as all applications follow the protocol. An application not using the protocol will cause problems however. It seems that the database system should be able to enforce the locking that I require. Surely others have database entities that are stored in this fashion. How are you handling locking for modification? All comments are welcome. -- Brett M. Bergquist, Principal Engineer | "Remind me" ... "to write an General DataComm, Inc., | "article on the compulsive reading Middlebury, CT 06762 | of news." - Stranger in a Strange Land Email: bergquis@nms.gdc.com