[Info-Ingres] readlock=nolock but table is locked
Mark
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-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Roy Hann
> 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.
> 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.)
> 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
Karl Schendel
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. :-)
Karl
lin.a.b...@uk.bnpparibas.com
that if you did not specify 'FOR READ ONLY' when you opened a cursor - it
always took out a write lock.
Lin
Internet
spec...@processed.almost.meat
Sent by: info-ingr...@kettleriverconsulting.com
10/03/2011 12:45
Please respond to
info-...@kettleriverconsulting.com
To
info-...@kettleriverconsulting.com
cc
Subject
Re: [Info-Ingres] readlock=nolock but table is locked
Mark wrote:
--
Roy
___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.
Please refer to http://www.bnpparibas.co.uk/en/information/legal_information.asp?Code=ECAS-845C5H for additional disclosures.
Roy Hann
> 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
Mark
<spec...@processed.almost.meat> wrote:
>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.
>> 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.
>> 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.
>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.
>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.
_______________________________________________
Karl Schendel
On Mar 10, 2011, at 8:41 AM, Roy Hann wrote:
> 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
Roy Hann
>>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/sql-reference-guide/1423-enable-securityaudit
http://docs.ingres.com/ingres/9.2/sql-reference-guide/1459-alter-user-syntax
http://docs.ingres.com/ingres/9.2/security-guide/4892-how-to-enable-security-auditing
and
http://community.ingres.com/w/files/b/b7/SC930.pdf
http://community.ingres.com/wiki/DBMS_Server_Query_Tracing
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011
Matt Charles
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.
Mark
>I think it depends on your defined isolation level in cbf. What isolation
>lvl did you configure?
What's isolation level?
Mark
<sche...@kbcomputer.com> wrote:
>
>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.
Roy Hann
>> http://community.ingres.com/wiki/DBMS_Server_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.
Not only has it been implemented, there's a patch to retro-fit it to
some older versions. I don't recall the patch number, but if you're
running 9.2 you have it already (or can get it).
For more see this UK IUA presentation
http://www.iua.org.uk/conference/Summer2009/Query_Recording_and_Playback.pdf
(You may need to register with the site to get access.)
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011
Roy Hann
>>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.
See
http://docs.ingres.com/ingres/9.2/sql-reference-guide/3499-open-syntax
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011
Karl Schendel
On Mar 10, 2011, at 11:34 AM, Mark wrote:
>
> 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.
It's on the OPEN, not the DECLARE.
exec sql open cursor C for readonly;
>
> I assumed that the default cursor select would be readonly if you have
> to declare if you want to change anything.
Yeah, you'd imagine that if you didn't DECLARE the cursor as
FOR UPDATE, then opening the cursor would be implicitly
readonly. Unfortunately you'd be wrong. :-)
If you don't explicitly open the cursor for readonly, you still
get update locking on the cursor, the reason being that
you can still DELETE WHERE CURRENT of cursor even
if you don't declare it as FOR UPDATE.
Bottom line is that you need the FOR READONLY clause on
the OPEN if you want read-locking.
Karl
Roy Hann
> On Thu, 10 Mar 2011 13:27:54 +0100, hans.wei...@mmm.com wrote:
>
>>I think it depends on your defined isolation level in cbf. What isolation
>>lvl did you configure?
>
> What's isolation level?
See
http://docs.ingres.com/ingres/9.2/sql-reference-guide/3604-session-isolation-level
Locking and lockmodes are proprietary and offer limited semantics.
Isolation levels are ANSI/ISO standard, more versatile, and give the
DBMS vendor more freedom to implement consistency control in whatever
way they like.
Just one reason (among several) to prefer isolation levels over
programmatic control of locking is that you can use the READ COMMITTED
isolation level to allow a reader to run more concurrently with a
writer without risking the dirty reads that happen when you use
READLOCK=NOLOCK.
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011
Mark
<sche...@kbcomputer.com> wrote:
>
>On Mar 10, 2011, at 11:34 AM, Mark wrote:
>>
>> 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.
>
>It's on the OPEN, not the DECLARE.
>
>exec sql open cursor C for readonly;
Ah!
>> I assumed that the default cursor select would be readonly if you have
>> to declare if you want to change anything.
>
>Yeah, you'd imagine that if you didn't DECLARE the cursor as
>FOR UPDATE, then opening the cursor would be implicitly
>readonly. Unfortunately you'd be wrong. :-)
>If you don't explicitly open the cursor for readonly, you still
>get update locking on the cursor, the reason being that
>you can still DELETE WHERE CURRENT of cursor even
>if you don't declare it as FOR UPDATE.
>
>Bottom line is that you need the FOR READONLY clause on
>the OPEN if you want read-locking.
I wasn't aware of this and the documentation does seem to be a bit
ambiguous. However I accept the explanation, thanks.
I wonder if Oracle has the same behaviour? (The code needs to work
with Oracle too).
Paul White
occurring ones.
E_DM9042_PAGE_DEADLOCK
E_DM9043_LOCK_TIMEOUT
E_DM9043_LOCK_TIMEOUT escalating to table lock
E_DM9044_ESCALATE_DEADLOCK
E_DM9045_TABLE_DEADLOCK
E_DM9071_LOCK_TIMEOUT_ONLINE_CKP
Paul
Mark
<shift7s...@gmail.com> wrote:
>Be sure to confirm the exact error code. These are some of the commonly
>occurring ones.
>
>E_DM9042_PAGE_DEADLOCK
>E_DM9043_LOCK_TIMEOUT
>E_DM9043_LOCK_TIMEOUT escalating to table lock
>E_DM9044_ESCALATE_DEADLOCK
>E_DM9045_TABLE_DEADLOCK
>E_DM9071_LOCK_TIMEOUT_ONLINE_CKP
Both of the following error codes are present:
E_CL1002_LK_TIMEOUT Lock timed out
E_DM9043_LOCK_TIMEOUT Timeout occurred
Roy Hann
>> I wonder if Oracle has the same behaviour? (The code needs to work
>> with Oracle too).
>
> Oracle abstracts itself from the ANSI isolation levels; it uses a
> different model (MVCC).So when you say 'READ COMMITTED' in Oracle it
> has different semantics; i.e. it does not lock anything
That's pretty much correct, although the isolation levels are defined in
terms of anomalies that will be visible, rather than locking. So when
you choose READ COMMITTED isolation you are saying that you are willing
to accept more anomalies than you would see at a stricter isolation
level, such as REAPEATABLE READ say, on the assumption that you will
get better concurrency in return. One does not usually ask for a
relaxed isolation level in order to actually see anomalies so the fact
that MVCC does a better job of concealing them than a particular
isolation level expects is acceptable behaviour.
> Ingres has MVCC too.Check <a href="http://community.ingres.com/wiki/
> MVCC">MVCC wiki </a>
Absolutely. This is only feature of Oracle that I've ever been jealous
of, and it's long overdue. Programmers pretty much assume MVCC and
have done for 10-15 years. That how this thread started. Once the
Ingres 10 implementation matures we'll wonder how we did without it. :-)
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011
Roy Hann
> The custom locking schemes like READLOCK etc, predate the ANSI
> standard and that is why the still exist?
Yep. When did the first completed SQL standard appear? Circa 1986?
commercial Ingres was 7 years old by then, and Oracle not much younger.
Roy Hann
> On Mar 13, 8:39 pm, Roy Hann <specia...@processed.almost.meat> wrote:
>> nikosv wrote:
>> >> I wonder if Oracle has the same behaviour? (The code needs to work
>> >> with Oracle too).
>>
>> > Oracle abstracts itself from the ANSI isolation levels; it uses a
>> > different model (MVCC).So when you say 'READ COMMITTED' in Oracle it
>> > has different semantics; i.e. it does not lock anything
>>
>> That's pretty much correct, although the isolation levels are defined in
>> terms of anomalies that will be visible, rather than locking.
>
> yes like in the sense that the vendor can implement the underlying
> locking scheme of his liking to match the anomaly described by the
> isolation level
Or not even use locking at all. Anything that produces the right effect
will do. And whatever is used can be changed, provided it continues to
produce the right effect. For example Adrian Hudnott presented a novel
MVCC algorithm at last years UK IUA conference which ought to permit
even better performance than conventional MVCC.
You mentioned abstraction earlier. The isolation level concept is
nicely abstract and makes no prescriptions about how it must be
implemented (which it couldn't, being very late to the party).
It seems to me that it's an example of standardization at it's best. Too
often the ANSI/ISO standard goes off on fantastical speculations
unconstrained by where the products and vendors really are (or want to
be). In this case they had to think harder and they came up with a
good answer.