RE: Named user lock facility in IDS?

Thanks for the suggestion Madison. I have a problem in that the database does not use transactions... Also, even if it did would this work as I thought the COMMIT would release all locks in the transaction, so if process A locked rows B and C and then unlocked row B then row C would also be unlocked too... Am I missing something obvious here? TIA. David -----Original Message----- From: Madison Pruet [mailto:mpruet1@verizon.net] Sent: 19 July 2007 13:08 To: informix-list@iiug.org Subject: Re: Named user lock facility in IDS? David N. Heydon wrote: > I wonder if someone could comment on if Informix IDS has a named user > locking facility. I'm looking for the ability to lock things on the database > server without being restricted by tables/rows/transactions etc. I would > like to use this for all sorts of things within applications where > controlled access is required to a given resource, i.e. access to a file on > a filesystem,, > > I was looking at Oracle recently and noticed it includes a facility like > this in a package called DBMS_LOCK. See > http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lock.htm > for further info. > > Any thoughts on how to achieve something similar with Informix IDS would be > most welcome! > > Cheers, > > David > > Create a table call is something like "lock_table". Make it "lock mode row". create table lock_table { lock_name varchar(255) primary key, hold_lock integer } lock mode row; To 'allocate a lock', simply insert a row into the lock_table. To set the lock "update lock_table set hold_lock = hold_lock + 1 where lock_name = "my lock"; To release the lock - commit. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On Jul 20, 5:59 am, "David N. Heydon" <david.hey...@LangdonSystems.com> wrote: > Thanks for the suggestion Madison. I have a problem in that the database > does not use transactions... Also, even if it did would this work as I > thought the COMMIT would release all locks in the transaction, so if process > A locked rows B and C and then unlocked row B then row C would also be > unlocked too... Am I missing something obvious here? > > TIA. > > David > > -----Original Message----- > From: Madison Pruet [mailto:mpru...@verizon.net] > Sent: 19 July 2007 13:08 > To: informix-l...@iiug.org > Subject: Re: Named user lock facility in IDS? > > David N. Heydon wrote: > > I wonder if someone could comment on if Informix IDS has a named user > > locking facility. I'm looking for the ability to lock things on the > database > > server without being restricted by tables/rows/transactions etc. I would > > like to use this for all sorts of things within applications where > > controlled access is required to a given resource, i.e. access to a file > on > > a filesystem,, > > > I was looking at Oracle recently and noticed it includes a facility like > > this in a package called DBMS_LOCK. See > > http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_l...>; for further info. > > > Any thoughts on how to achieve something similar with Informix IDS would > be > > most welcome! > > > Cheers, > > > David > > Create a table call is something like "lock_table". Make it "lock mode > row". > > create table lock_table { > lock_name varchar(255) primary key, > hold_lock integer > } lock mode row;> > To 'allocate a lock', simply insert a row into the lock_table. > > To set the lock "update lock_table set hold_lock = hold_lock + 1 where > lock_name = "my lock"; > > To release the lock - commit. Madison's point is: if you want to make certain that only one user at a time can perform an operation (it sounds like that's what you want), then with a lock_table in existence, all user's would be required to insert or update a row in the lock_table with a particular lock_name value. That user would do so within a transaction, holding the lock by NOT committing or rolling back the transaction until he had completed all related work. Once the work is completed the COMMIT will release the lock allowing another user in to acquire the lock. If that's not what you intended, then be more specific. Pointing us at the Oracle manuals is NOT a good way to help us understand what this DBMS_LOCK function is supposed to accomplish, or even if that would be the best solution to your problem if you were using Oracle. Most of us avoid reading Oracle manuals for fear of contracting some dread incurable disease. The following is intended for all posters to this NG, not just David: PLEASE PLEASE DO NOT POST your unworkable possible solution and ask us how to make it work. It is FAR BETTER to post the actual problem and ask us how we would or in fact DO solve it ourselves. If you can find it, or Jonathan posts it again, see the paper on Newgroup Etiquette someone once wrote. Art S. Kagel

>From: "Art S. Kagel" <art.kagel@gmail.com> [SNIP] Well, some of us *have* to read the oracle manuals. :-( >From what I can tell, its an oracle feature such that you can create a lock on resources such that no one else can touch those resources while you hold that lock. It appears to be an extension of their internal locking mechanisms. I asked one of my local Oracle gurus and he cringed on it. Said he only used it once because he was forced to. In that instance they wanted to lock the database so that no one could do anything while this banking process was in operation. He felt that it was way too heavy handed.... I think that in Informix you'd have a single user put a lock on the specific tables to have the same effect. HOWEVER, when the OP started talking about wanting to lock external resources like files in the file system, I think that there may be some confusion as to what he wants. I do agree with Art, however, TANSTAAFL and we're not here to consult and provide free advice on your design issues ... _________________________________________________________________ http://liveearth.msn.com