[Info-ingres] Row locking

[Mark]

I have an esqlc program which attempts to use row-level locking :

EXEC SQL SET LOCKMODE SESSION WHERE LEVEL = ROW;

EXEC SQL CREATE TABLE row_lock_test_table
(
field1 varchar(30) not null not default,
field2 integer not null not default,
field3 date not null not default
);

EXEC SQL MODIFY row_lock_test_table TO ISAM UNIQUE ON field1,
field2;

EXEC SQL INSERT INTO row_lock_test_table
VALUES( 'ONE', 1, date('now') );
EXEC SQL INSERT INTO row_lock_test_table
VALUES( 'TWO', 2, date('now') );
EXEC SQL INSERT INTO row_lock_test_table
VALUES( 'THREE', 3, date('now') );

EXEC SQL COMMIT;

// Now lock row 'THREE'
EXEC SQL UPDATE row_lock_test_table SET field3 = date('now') WHERE
field1 = 'THREE' AND field2 = 3;

printf("Waiting for %d seconds ...\n", seconds);
sleep(seconds);

During the sleep I attempt to update a different record from another
session but it hangs as if the whole table is locked. How can I get
row-level locking to work?
--
(\__/) M.
(='.'=) If a man stands in a forest and no woman is around
(")_(") is he still wrong?

_______________________________________________
Info-ingres mailing list
Info-...@lists.planetingres.org
http://lists.planetingres.org/mailman/listinfo/info-ingres

[Mark]

On Fri, 27 Nov 2015 02:29:55 -0800 (PST), Tim
<goo...@timellis.demon.co.uk> wrote:

>What is your Page Size set to? IIRC Row Locking requires you to have a
>larger size than the default 2K pages, but you don't get an error if
>you try to set lockmode to table level locking with 2K pages...

I have increased the page size to 8K but it makes no difference ....

[Roy Hann]

Mark wrote:

> I have an esqlc program which attempts to use row-level locking :

[snip]

> During the sleep I attempt to update a different record from another
> session but it hangs as if the whole table is locked. How can I get
> row-level locking to work?

Is the other session also using row-level locking?

If it is using default (page-level) locking it can't acquire the
exclusive page lock it requires and will wait, as you are seeing.

Roy

[Mark]

On Fri, 27 Nov 2015 15:48:26 +0000 (UTC), Roy Hann
<spec...@processed.almost.meat> wrote:

>Mark wrote:
>
>> I have an esqlc program which attempts to use row-level locking :
>
>[snip]
>
>> During the sleep I attempt to update a different record from another
>> session but it hangs as if the whole table is locked. How can I get
>> row-level locking to work?
>
>Is the other session also using row-level locking?

Yes.

>If it is using default (page-level) locking it can't acquire the
>exclusive page lock it requires and will wait, as you are seeing.

Good suggestion -- but that isn't it.

[Roy Hann]

Mark wrote:

> On Fri, 27 Nov 2015 15:48:26 +0000 (UTC), Roy Hann
> <spec...@processed.almost.meat> wrote:
>
>>Mark wrote:
>>
>>> I have an esqlc program which attempts to use row-level locking :
>>
>>[snip]
>>
>>> During the sleep I attempt to update a different record from another
>>> session but it hangs as if the whole table is locked. How can I get
>>> row-level locking to work?
>>
>>Is the other session also using row-level locking?
>
> Yes.
>
>>If it is using default (page-level) locking it can't acquire the
>>exclusive page lock it requires and will wait, as you are seeing.
>
> Good suggestion -- but that isn't it.

I ran your code in 10.10.0 (a64.win/125)GPL and it works as expected; no
blocking.

With ING_SET=set lock_trace, from rowtest.exe (creates table,
inserts rows, locks a row):

LOCK: MVCC PHYS Mode: IS Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: TABLE PHYS Mode: X Timeout: 0
Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
LOCK: MVCC PHYS Mode: IS Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: TABLE PHYS Mode: X Timeout: 0
Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
LOCK: MVCC PHYS Mode: IS Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: TABLE PHYS Mode: X Timeout: 0
Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
LOCK: MVCC PHYS Mode: IS Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: TABLE PHYS Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: PAGE PHYS,LOCL,NOIN,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
LOCK: ROW NOWT,STAT,LOCL,QUTM Mode: U Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,2)
LOCK: PAGE LOCL,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
LOCK: ROW STAT,LOCL,QUTM Mode: U Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,2)
LOCK: ROW STAT,LOCL,QUTM Mode: X Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,2)
LOCK: PAGE LOCL,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
-------------------------------------------------------------------
press enter to continue...

And from updater.exe (updates a different row):

C:\Users\Roy\Desktop\Junk>updater
Updating...
LOCK: MVCC PHYS Mode: IS Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: TABLE PHYS Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table)
LOCK: PAGE PHYS,LOCL,NOIN,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
LOCK: ROW NOWT,STAT,LOCL,QUTM Mode: U Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,0)
LOCK: PAGE LOCL,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
LOCK: ROW STAT,LOCL,QUTM Mode: U Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,0)
LOCK: ROW STAT,LOCL,QUTM Mode: X Timeout: 0
Key: (sandbox,row_lock_test_table,0.0,0)
LOCK: PAGE LOCL,QUTM Mode: IX Timeout: 0
Key: (sandbox,row_lock_test_table,0.0)
-------------------------------------------------------------------
..and now done!

What version are you running? Do you have ING_SET (in your local
environment or the Ingres symbol table) set to anything confounding?
What is the DBMS system_lock_level set to in CBF?

Roy

[Roy Hann]

Roy Hann wrote:


> I ran your code in 10.10.0

Obviously I meant 10.1

[Mark]

On Fri, 27 Nov 2015 17:08:32 +0000 (UTC), Roy Hann
<spec...@processed.almost.meat> wrote:

>Roy Hann wrote:
>
>
>> I ran your code in 10.10.0
>
>Obviously I meant 10.1

10.1.0 here also.

[Laframboise, André (BAC/LAC)]

Hi Mark,

By default, Ingres uses 2K pages if you don't specify anything else. 2K pages do not support row level locking.

First you have to enable other page sizes in the server, from CBF, go into DBMS/CACHE select 'ON' for the desired page size.

Second, add the page_size parameter in your create or modify statement. i.e. page_size=4096.


Andre

[Allan Biggs]

I want to do some testing on a live IngresNet database from both a Linux
box and W7 enterprise PC


I want to "set lockmode session where readlock = nolock"

so that I don't lock any tables/rows in a live database.
I really want to do it for an individual database or a node is it possible
to do this on both platforms

I found this in the documentation

setenv ING_SET_DBNAME 'set-statement {; set-statement}'

but how do you specify the DBNAME if the database is defined by NETUTIL
e.g. as node1::db_allan


thanks
Allan

This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.

Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean

http://www.DuPont.com/corp/email_disclaimer.html

[Karl Schendel]

> On Feb 24, 2016, at 7:12 AM, Allan Biggs <Allan...@GBR.dupont.com> wrote:
>
> I want to do some testing on a live IngresNet database from both a Linux box and W7 enterprise PC
> I want to "set lockmode session where readlock = nolock"
>
> so that I don't lock any tables/rows in a live database.
> I really want to do it for an individual database or a node is it possible to do this on both platforms
>
> I found this in the documentation
>
> setenv ING_SET_DBNAME 'set-statement {; set-statement}'
>
> but how do you specify the DBNAME if the database is defined by NETUTIL e.g. as node1::db_allan

I would assume that you just take the dbname, so ING_SET_db_allan (or perhaps
ING_SET_DB_ALLAN, I don't know if it uppercases it or takes the name literally.)

Karl

[Allan Biggs]

I later discovered (for windows)

SET ING_SET=SET LOCKMODE SESSION WHERE READLOCK=NOLOCK
and
SET ING_SET_node1::db_allan =SET LOCKMODE SESSION WHERE READLOCK=NOLOCK

I was just surprised that "::" is OK in the environment variable
Is there a way of checking that the lockmode has been set in the
ingres_net database?

Thanks
Allan

_______________________________________________
Info-ingres mailing list
Info-...@lists.planetingres.org
http://lists.planetingres.org/mailman/listinfo/info-ingres

[Roy Hann]

Allan Biggs wrote:

> I later discovered (for windows)
>
> SET ING_SET=SET LOCKMODE SESSION WHERE READLOCK=NOLOCK
> and
> SET ING_SET_node1::db_allan =SET LOCKMODE SESSION WHERE READLOCK=NOLOCK
>
> I was just surprised that "::" is OK in the environment variable
> Is there a way of checking that the lockmode has been set in the
> ingres_net database?

As in a dbmsinfo() call? No.

Probably the best thing you can do is connect using sql or isql, SET
LOCK_TRACE and try some stuff to see what happens.

If sql picks up the setting then your application will too.

Roy

PS: Rather than twiddle lockmodes I'd be inclined to set the session
isolation level to read uncommitted. That has the benefit that it makes
the session read-only, which is prudent when you can't be confident of
what you're being shown in the database.

[Roy Hann]

Allan Biggs wrote:

> I want to set "ing_set" on a Linux box just for the current job (I will
> unset it after the job has run).
>
> Presumably the error here mean that only the Ingres installation account
> can use ingsetenv and set it globally?
>
> Is there a way around this on Linux? I am trying to set up ing_set in the
> current bash script and then delete it when the script exits.

It already works the way you want (in Linux, other unixen, and on
Windows)

If you set ING_SET in your local environment (i.e. export ING_SET='...'
in your script) it overrides ING_SET in the global symbol table.

[Martin Bowes]

At your linux prompt type: echo $SHELL

It will respond with something like /bin/bash or /bin/tcsh.

If bash then do: export ING_SET=yoursetting
If tcsh then do: setenv ING_SET yoursetting

If the shell response is neither of the indicated ones then let me know.

Martin Bowes

From: Allan Biggs [mailto:Allan...@GBR.dupont.com]
Sent: 07 March 2016 14:57
To: info-...@lists.planetingres.org
Subject: [Info-ingres] Fw: error trying to use ing_set on Linux



I want to set "ing_set" on a Linux box just for the current job (I will unset it after the job has run).

Presumably the error here mean that only the Ingres installation account can use ingsetenv and set it globally?

Is there a way around this on Linux? I am trying to set up ing_set in the current bash script and then delete it when the script exits.

My Linux/bash experience is almost non-existent :-)

thanks
Allan


> ingsetenv ing_set "set lockmode session where readlock = nolock"
ingsetenv: unable to set variable "ing_set"
E_CL1405_NM_STOPN NM[sg]tIngAt: Unable to open symbol table
only the Ingres super-user can use ingsetenv
Mon Mar 07@14:32:19





From: Karl Schendel <sche...@kbcomputer.com<mailto:sche...@kbcomputer.com>>
To: "info-...@lists.planetingres.org<mailto:info-...@lists.planetingres.org>" <info-...@lists.planetingres.org<mailto:info-...@lists.planetingres.org>>
Date: 24/02/2016 15:10
Subject: Re: [Info-ingres] ing_set on linux and windows
Sent by: info-ingr...@lists.planetingres.org<mailto:info-ingr...@lists.planetingres.org>
________________________________

> On Feb 24, 2016, at 7:12 AM, Allan Biggs <Allan...@GBR.dupont.com<mailto:Allan...@GBR.dupont.com>> wrote:
>
> I want to do some testing on a live IngresNet database from both a Linux box and W7 enterprise PC
> I want to "set lockmode session where readlock = nolock"
>
> so that I don't lock any tables/rows in a live database.
> I really want to do it for an individual database or a node is it possible to do this on both platforms
>
> I found this in the documentation
>
> setenv ING_SET_DBNAME 'set-statement {; set-statement}'
>
> but how do you specify the DBNAME if the database is defined by NETUTIL e.g. as node1::db_allan

I would assume that you just take the dbname, so ING_SET_db_allan (or perhaps
ING_SET_DB_ALLAN, I don't know if it uppercases it or takes the name literally.)

Karl

_______________________________________________
Info-ingres mailing list
Info-...@lists.planetingres.org<mailto:Info-...@lists.planetingres.org>

[Allan Biggs]

I want to set "ing_set" on a Linux box just for the current job (I will
unset it after the job has run).

Presumably the error here mean that only the Ingres installation account
can use ingsetenv and set it globally?

Is there a way around this on Linux? I am trying to set up ing_set in the
current bash script and then delete it when the script exits.

My Linux/bash experience is almost non-existent :-)

thanks
Allan


> ingsetenv ing_set "set lockmode session where readlock = nolock"
ingsetenv: unable to set variable "ing_set"
E_CL1405_NM_STOPN NM[sg]tIngAt: Unable to open symbol table
only the Ingres super-user can use ingsetenv
Mon Mar 07@14:32:19





From: Karl Schendel <sche...@kbcomputer.com>
To: "info-...@lists.planetingres.org"
<info-...@lists.planetingres.org>
Date: 24/02/2016 15:10
Subject: Re: [Info-ingres] ing_set on linux and windows
Sent by: info-ingr...@lists.planetingres.org

> On Feb 24, 2016, at 7:12 AM, Allan Biggs <Allan...@GBR.dupont.com>

wrote:
>
> I want to do some testing on a live IngresNet database from both a Linux
box and W7 enterprise PC
> I want to "set lockmode session where readlock = nolock"
>
> so that I don't lock any tables/rows in a live database.
> I really want to do it for an individual database or a node is it
possible to do this on both platforms
>
> I found this in the documentation
>
> setenv ING_SET_DBNAME 'set-statement {; set-statement}'
>
> but how do you specify the DBNAME if the database is defined by NETUTIL
e.g. as node1::db_allan

I would assume that you just take the dbname, so ING_SET_db_allan (or
perhaps
ING_SET_DB_ALLAN, I don't know if it uppercases it or takes the name
literally.)

Karl

_______________________________________________
Info-ingres mailing list
Info-...@lists.planetingres.org