Re: Locking (was Globals in 4gl - feature or bug?)

> marcog@ctonline.it (marco greco) writes: > Informix 4gl Reference Manual, volume two: > 1. only one lock can apply to a table at any given time. That is if a user locks a table (in either share or exclusive mode), no other user can lock that table in either mode until the first user unlocks it > > Informix Guide to SQL, Reference: > .....The Lock table statement fails if the table is already locked by another process. > ..... > *SE* The INFORMIX-SE database engine does not permit more than one user to lock a table in share mode. (???? it was stated that only one lock can exist at a given moment in time!!!!) > > This is very different than saying: > > *SE*: only one lock can apply to a table at any given time. That is, if a user locks a table (in either share or exclusive mode), no other user can lock that table in either mode until the first user unlocks it > > *OL*: only one kind of lock can apply to a table at any given time. Many shared locks are allowed concurently, while only one exclusive lock can exist at any givem time. > > And note that lock behaviour has serious implications in application design... Yes it does. We had applications that under SE would do lock in share mode and noone else could lock the same table. We used this to make sure only one user did critical updates to some tables. That still works in a way. Noone can update the table if it is locked in share mode. The problem under OnLine 7 however is that any number of users (or applications realy) can execute the lock statement with share mode. It isn't until an actual update is attemted that the we find out someone else has a lock on the table. We can't lock in exclusive mode because we do want others to be able to read from the table while we do the updates. What we have to do? The first thing I can think of is to attempt a dummy update after the lock in share mode. If it works I was probably the first one to do it. If another program also executes a lock in share mode on the same table I probably can't update it even though I was the first to lock it. The manual doesn't say, and I haven't tried yet. Even if the next program does a dummy update, finds the table locked and immediately unlocks it I may get problems. The update process is typically a batch routine. There is a high probability that an update will be attempted by this process while the other one has the lock (before its unlock statement). The batch routine will fail. All of a sudden it couldn't update the table. What I do then? I haven't decided yet. Use a special table for marking locks only? Bad... I don't know why many programs can now place a shared lock on a table. There probably is one I haven't thought about. Anyone any ideas? Shouldn't there be an option that works like the share mode locks in SE? The database where this is a problem is without transactions by the way. With transactions we could program differently. Nils.Myklebust@ccmail.telemax.no NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway My opinions are those of my company