Re: Named user lock facility in IDS?

On Jul 20, 5:59 am, "David N. Heydon"
<david.hey...@xxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxxxx]
Sent: 19 July 2007 13:08
To: informix-l...@xxxxxxxx
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

.

Relevant Pages

  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • Re: USER LOCKING PROBLEM
    ... issue a begin transaction as well. ... But the length of time that a lock is held for is dependant on ... Or also possibly in the ODBC connection. ... a COMMIT to be issued after ever SQL Statement. ...
    (microsoft.public.sqlserver.server)
  • Re: controlling lock order in transactions
    ... It seems to be creating the @@identity ok before the COMMIT in my testing. ... > first transaction is not commited so there is no @@identinty created. ... >> An Insert gets an Exclusive Lock which is held for the whole transaction. ... >> Will changing orders in the FROM clause cause locks to be taken in ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... first transaction is not commited so there is no @@identinty created. ... >I have the following scenario which causes deadlocks in stored procedures: ... > COMMIT ... > An Insert gets an Exclusive Lock which is held for the whole transaction. ...
    (microsoft.public.sqlserver.programming)