readlock=nolock but table is locked

#1

Hi,

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.
Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

Killing program 1 unblocks program 2.

Is this an Ingres bug?
--
(\__/) M.
(='.'=) Due to the amount of spam posted via googlegroups and
(")_(") their inaction to the problem. I am blocking some articles
posted from there. If you wish your postings to be seen by
everyone you will need use a different method of posting.

#2

Hi Mark,

I think it depends on your defined isolation level in cbf. What isolation
lvl did you configure?

Hans

3M Deutschland GmbH

Sitz: 41453 Neuss; Handelsregister: B 1878 Amtsgericht Neuss
Geschäftsführer: Günter Gressler; Josef Mrozek; Theo Nöcker
Managing Director: Günter Gressler; Vorsitzender des Aufsichtsrates:
Kurt-Henning Wiethoff

From: Mark <i (AT) dontgetlotsofspamanymore (DOT) invalid>

To: <info-ingres (AT) kettleriverconsulting (DOT) com>

Date: 10.03.2011 11:52

Subject: [Info-Ingres] readlock=nolock but table is locked

Sent by: <info-ingres-bounces (AT) kettleriverconsulting (DOT) com>

Hi,

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.
Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

Killing program 1 unblocks program 2.

Is this an Ingres bug?
--
(\__/) M.
(='.'=) Due to the amount of spam posted via googlegroups and
(")_(") their inaction to the problem. I am blocking some articles
posted from there. If you wish your postings to be seen by
everyone you will need use a different method of posting.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#3

Mark wrote:

Quote:

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.

I hope you fully understand ALL the implications of that.

Quote:

Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

That can't be right. In the first place if the application is using
READLOCK=NOLOCK it won't attempt a lock, won't wait, and won't time
out, so you won't see a lock timeout in the log. Furthermore if the lock
requests do timeout then then neither the application nor the server
is hanging. (The application might be looping and re-waiting though,
making it look like it's hanging.)

Quote:

Killing program 1 unblocks program 2.

Is this an Ingres bug?

More likely an application bug. Use security query_text auditing or
trace point SC930 to see what the applications are doing.

Another possibility is that there is a third participant you don't know
about that is locking (taking an ordinary lock or a control lock).

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

#4

On Mar 10, 2011, at 4:37 AM, Mark wrote:

Quote:

Hi,

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.
Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

Did the reader open the cursor FOR READONLY?

Because if it didn't, it's going to take update locks, and
the readlock setting will be irrelevant.

I learned that lesson the hard way, years ago, and it
was sufficiently painful that it stuck with me. :-)

Karl

#5

Karl Schendel wrote:

Quote:

I learned that lesson the hard way, years ago, and it
was sufficiently painful that it stuck with me. :-)

What were you doing using a cursor anyway? You know better. :-)

I admit I missed the FOR READONLY trap here because I rarely resort to
using the beastly things.

But while we're on the subject, opening a cursor for READONLY (if it can
be readonly) will deliver *sets* of rows to the application, rather than
a row at a time. So not only will it not block, it will be a heck of a
lot faster too. Though not as fast as if you didn't use a cursor at
all...

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

#6

On Thu, 10 Mar 2011 12:45:50 +0000 (UTC), Roy Hann
<specially (AT) processed (DOT) almost.meat> wrote:

Quote:

Mark wrote:

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.

I hope you fully understand ALL the implications of that.

Yes.

Quote:

Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

That can't be right. In the first place if the application is using
READLOCK=NOLOCK it won't attempt a lock, won't wait, and won't time
out, so you won't see a lock timeout in the log.

I know this *should* not be happening, but it is.

Quote:

Killing program 1 unblocks program 2.

Is this an Ingres bug?

More likely an application bug.

More likely maybe but these programs are quite simple and work 100% OK
on all but one platform. A similar issue was found in entirely
different programs and was never solved.

Quote:

Use security query_text auditing or
trace point SC930 to see what the applications are doing.

Never heard of those. I'll have to look them up.

Quote:

Another possibility is that there is a third participant you don't know
about that is locking (taking an ordinary lock or a control lock).

I have been assured that this is not the case. I do not actually have
access to the system where it fails though.
--
(\__/) M.
(='.'=) Due to the amount of spam posted via googlegroups and
(")_(") their inaction to the problem. I am blocking some articles
posted from there. If you wish your postings to be seen by
everyone you will need use a different method of posting.

#7

On Mar 10, 2011, at 8:41 AM, Roy Hann wrote:

Quote:

Karl Schendel wrote:

I learned that lesson the hard way, years ago, and it
was sufficiently painful that it stuck with me. :-)

What were you doing using a cursor anyway? You know better. :-)

This was way, WAY back when we were using Ingres to simulate
a bunch of COBOL files. The COBOL code was row at a time, so
my pseudo-generic interface layer pretty much had to use cursors.

That stuff didn't last too long, we replaced it with 4GL. The COBOL
was a purchased system to get things bootstrapped.

Besides the FOR READONLY thing, the experience taught me
that cursors are wrong, broken, and evil. I think I've used them
exactly twice since then. (and in both cases, the table design
was defective but set in concrete.)

Karl

#8

Mark wrote:

Quote:

Use security query_text auditing or
trace point SC930 to see what the applications are doing.

Never heard of those. I'll have to look them up.

Very useful to know about.

See:

http://docs.ingres.com/ingres/9.2/sq...-securityaudit
http://docs.ingres.com/ingres/9.2/sq...er-user-syntax
http://docs.ingres.com/ingres/9.2/se...urity-auditing

and

http://community.ingres.com/w/files/b/b7/SC930.pdf
http://community.ingres.com/wiki/DBM..._Query_Tracing

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

#9

Quote:

http://community.ingres.com/wiki/DBM..._Query_Tracing

Has the capture / playback stuff mentioned in the link above been
implemented? It sounds very useful! I had no idea such a feature
existed in Ingres.

#10

On Thu, 10 Mar 2011 08:01:47 -0500, Karl Schendel
<schendel (AT) kbcomputer (DOT) com> wrote:

Quote:

On Mar 10, 2011, at 4:37 AM, Mark wrote:

Hi,

Ingres 9.2 on AIX 6.1

I have two programs:
1. Doing a cursor select on a table.
2. Doing an insert on the same table.

I am 100% sure that "readlock=nolock" is set for all sessions.
Both transactions hang and I can see loads of lock timeouts in the
ingres error log.

Did the reader open the cursor FOR READONLY?

Because if it didn't, it's going to take update locks, and
the readlock setting will be irrelevant.

I learned that lesson the hard way, years ago, and it
was sufficiently painful that it stuck with me. :-)

I can't see anything in the documentation that mentions "FOR READONLY"
syntax. I can see a "FOR ... UPDATE OF column". I am not using the
latter.

I assumed that the default cursor select would be readonly if you have
to declare if you want to change anything.

--
(\__/) M.
(='.'=) Due to the amount of spam posted via googlegroups and
(")_(") their inaction to the problem. I am blocking some articles
posted from there. If you wish your postings to be seen by
everyone you will need use a different method of posting.