maxdb@lists.mysql.com
[Top] [All Lists]

RE: Row locking problem. - Vtrace (new driver)

Subject: RE: Row locking problem. - Vtrace new driver
From: "Michael Andrewes"
Date: Tue, 12 Apr 2005 18:09:44 +1000
Hi,

Perfect, I'll upgrade in the next few weeks.


Regards
Michael Andrewes 

-----Original Message-----
From: Zabach, Elke [mailto:elke.zabach@xxxxxxx] 
Sent: Tuesday, 12 April 2005 12:21 AM
To: Michael Andrewes; maxdb@xxxxxxxxxxxxxxx
Subject: AW: Row locking problem. - Vtrace (new driver)

Michael Andrewes wrote:

> 
> Hi,
> 
> 
> I'm still watching the mailing list for a fix to this? Should there be 
> another area to watch? Releaser and changelogs perhaps? (im not sure 
> where it's at).
> 
> Regards
> Michael Andrewes


As you wrote in the meantime, you are using a very old version 7.4.3.10.

With this version, we could reproduce the problem you found.
With version 7.4.3.32 (the newest given to open source), we could NOT
reproduce that problem.
Therefore we would like to ask for an upgrade because of this problem.

Elke
SAP Labs Berlin

> 
> -----Original Message-----
> From: Zabach, Elke [mailto:elke.zabach@xxxxxxx]
> Sent: Thursday, 27 January 2005 8:56 PM
> To: Michael Andrewes
> Subject: AW: Row locking problem. - Vtrace (new driver)
> 
> Hi,
> 
> did you tell us which kernel version you are using? I did not find any 
> info in those mails I searched through.
> 
> And the second vtrace shows the same situation as the first one:
> Update has locked one row, selecting uses the index, finds a primary 
> key in, checks the corresponding primary row (the same as was updated 
> by another task), receives the info that this row is exclusive locked 
> by another user, waits for the other user's commit, receives the info 
> that now the row is 'free' (in terms of lock), but instead checking 
> this row again and therefore 'finding' it, it jumps to the next (none 
> available) and returns the error 100.
> One guy knowing much more about this part of the kernel will check it.
> 
> Is it always the last indexvalue causing the trouble or do you know 
> anything else in common for the empty resultsets?
> 
> Elke
> SAP Labs Berlin
> 
> > -----Ursprüngliche Nachricht-----
> > Von: Michael Andrewes [mailto:mandrewes@xxxxxxxxxxxxxxxxxxx]
> > Gesendet: Donnerstag, 27. Januar 2005 04:59
> > An: Schroeder, Alexander; 'SAP DB mailing list'; Zabach, Elke
> > Betreff: RE: Row locking problem. - Vtrace (new driver)
> >
> >  Hi,
> >
> > I upgraded the jdbc driver to
> > package com.sap.dbtech.jdbc, SAP DB JDBC Driver, SAP AG, 7.4.4    Build
> > 003-000-002-502
> >
> >
> > And tried again, I get the same result.  Attached is a vtrace with 
> > the new driver.
> >
> >
> >
> > Regards
> > Michael Andrewes
> >
> >
> >
> > -----Original Message-----
> > From: Schroeder, Alexander [mailto:alexander.schroeder@xxxxxxx]
> > Sent: Monday, 24 January 2005 7:34 PM
> > To: Michael Andrewes
> > Subject: RE: Row locking problem. - Vtrace
> >
> > Hello Michael, could you please tell the version number of the used 
> > JDBC driver (java -jar sapdbc.jar -V). The trace looks as it may be 
> > quite old (although there was never an issue in JDBC that did show 
> > up like you said ...).
> >
> > Regards
> > Alexander Schröder
> >
> > > -----Original Message-----
> > > From: Michael Andrewes [mailto:mandrewes@xxxxxxxxxxxxxxxxxxx]
> > > Sent: Monday, January 24, 2005 3:44 AM
> > > To: 'Michael Andrewes'; Zabach, Elke; Schroeder, Alexander; 'SAP 
> > > DB mailing list'
> > > Subject: RE: Row locking problem. - Vtrace
> > >
> > > Hi,
> > >
> > >
> > > Attached is the vtrace, I had a quick look and can see my query 
> > > and error code etc (with error code 100, Row not found).
> > >
> > >
> > > As I mentioned, I do not check error codes, or assume anything has 
> > > failed, unless the JDBC driver actually throws an exception (then 
> > > I check error codes to decide what to do).
> > > Which is all I would expect to need to do.
> > >
> > > If I need to check error codes when exceptions are not thrown, 
> > > please let me know.
> > >
> > >
> > >
> > > Thankyou
> > > Michael Andrewes
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Michael Andrewes [mailto:mandrewes@xxxxxxxxxxxxxxxxxxx]
> > > Sent: Sunday, 23 January 2005 11:17 AM
> > > To: 'Zabach, Elke'; 'Schroeder, Alexander'; 'SAP DB mailing list'
> > > Subject: RE: Row locking problem.
> > >
> > > Hi,
> > >
> > > Sorry,  each mail has the same information, but I was trying to 
> > > cut down the length of the mail and simplify.
> > >
> > > By "Empty row"/"Empty Resultset"  I mean I get a resultset back, 
> > > with 0 rows in it.  However I say "empty row" as I was only 
> > > expecting one row.
> > > ** From now on, when I say "Empty Resultset", I mean that No 
> > > Exception is thrown, and there are no rows in the result set.
> > >
> > >
> > > >Or do you mean, that the resultset was empty, meaning error 100 
> > > >was
> > > returned?
> > > I am using the JDBC driver and do not check error codes unless an 
> > > exception in thrown by the JDBC driver.
> > > There is no exception thrown in this case.
> > >
> > >
> > > >Selects which are forced to wait for the commit/rollback of the 
> > > >transaction
> > > which at that time holds a lock for the next needed row, will do 
> > > that and not return something else. In case the request timeout-
> > > >value of x (usually 900) seconds expired, it will return
> > > with error 500.
> > > Normally, this is what happens.  However this error happens, I get 
> > > an empty resultset immediatley. My request timeout is default or 
> > > higher than the default OLTP instance.
> > >
> > >
> > > >The vtrace is something written cyclic, therefore it does not 
> > > >matter how
> > > long it will be written. But writing of the vtrace has to be 
> > > stopped immediately after occurance of the problem, otherwise 
> > > further
> > > >writing will overwrite the info needed.
> > > >What kind of transactions turn off the vtrace? Why?
> > > Ok, I can turn off the vtrace immediatley after this happens.
> > > Multiple instances of this same query would turn it off.
> > > Normally there are
> > > around 20+ clients trying to lock and update the property at any 
> > > one time when this occurs.
> > >
> > > If I manually enter a "select for update", the application WILL 
> > > block and eventually I will get an exception thrown.  As I said, I 
> > > cannot get replicate this in test as we have a slower DB and less 
> > > clients.
> > >
> > >
> > > >Seeing the vtrace will help us if we know exactly the
> > > statement we have
> > > >to
> > > look for; the result/the error the application saw. It is of no 
> > > use if we just receive the vtrace with the info: my application 
> > > had
> > > >some time ago
> > > (may be in the vtrace or not) some trouble with an empty 
> > > result-row/set.
> > >
> > >
> > > >But all of us checking your problem believe, that the
> > > error-handling in
> > > your application is not correct. Therefore: please check this and 
> > > let us know, if we can close this thread then.
> > > I have gone over it many times.
> > > If I need to check error codes, even when an exception is not 
> > > generated by the JDBC driver, then it's not correct.
> > > However if the JDBC driver generates an exception for all errors, 
> > > then my error checking is fine.  As basically it comes down to a 
> > > try-catch block.
> > > I would prefer not to include any, as that's not what this thread 
> > > is for.
> > >
> > >
> > >
> > > I will get a vtrace and send it in.
> > >
> > >
> > > Thankyou
> > > Michael Andrewes
> > >
> > >
> > > -----Original Message-----
> > > From: Zabach, Elke [mailto:elke.zabach@xxxxxxx]
> > > Sent: Saturday, 22 January 2005 2:18 AM
> > > To: Michael Andrewes; Schroeder, Alexander; SAP DB mailing list
> > > Subject: AW: Row locking problem.
> > >
> > > Michael Andrewes wrote:
> > > >
> > > > Hi,    (re-sent to put on mailing list)
> > > >
> > > > Thanks for getting back,  ok.
> > > >
> > > > There are no deletes in this instance, and also no inserts.  
> > > > Under normal circumstances, I would do an insert if the last
"select"
> > > > returned no rows, but that was in effect resetting an existing 
> > > > property
> > > with this problem.
> > > >
> > > > The final select gets an empty result set (I only expect
> > > one row), I
> > > > have since tried
> > > >
> > > > SELECT
> > > > SELECT FOR UPDATE
> > > > SELCTE FOR UPDATE
> > > > UPDATE
> > > > COMMIT
> > > >
> > > > With the same result.
> > > >
> > > >
> > > > When it happens there would actually usually be 10 separate 
> > > > connections, each processing up to 30 transactions per second.
> > > > It also might take a few hours for the problem to occur
> > > once I enable
> > > > the vtrace, is this a problem?
> > > >
> > > > Also many transactions would turn off the vtrace, I assume
> > > this wont
> > > > affect it? As once the first one does it we should have the vtrace?
> > > >
> > > >
> > > > Please let me know if putting in the vtrace with so many
> > > things going
> > > > on will be useful, and I'll put it in.
> > > >
> > > >
> > > >
> > > > Regards
> > > > Michael Andrewes
> > > >
> > > >
> > >
> > > You wrote several mails, but with different info and not the info 
> > > which allows us to see what may happen.
> > > Once you spoke of 'an emtpy row'? What do you mean? The resulting 
> > > row was filled with blanks, NULL-values? This will not be done by 
> > > the database system MaxDB. This only may happen if the application 
> > > does not check errors (for example error 100 or error 500) 
> > > returned by the select or fetch-command (to fetch the next 
> > > resultrow out of the kernel), but the application returns the 
> > > unfilled
> > > local(application-) variables.
> > >
> > > Or do you mean, that the resultset was empty, meaning error 100 
> > > was returned?
> > >
> > > Selects which are forced to wait for the commit/rollback of the 
> > > transaction which at that time holds a lock for the next needed 
> > > row, will do that and not return something else. In case the 
> > > request timeout-value of x (usually
> > > 900) seconds expired, it will return with error 500.
> > >
> > > The vtrace is something written cyclic, therefore it does not 
> > > matter how long it will be written. But writing of the vtrace has 
> > > to be stopped immediately after occurance of the problem, 
> > > otherwise further writing will overwrite the info needed.
> > > You said:
> > > > Also many transactions would turn off the vtrace, I assume
> > > this wont
> > > > affect it? As once the first one does it we should have the vtrace?
> > >
> > > What kind of transactions turn off the vtrace? Why?
> > >
> > > Seeing the vtrace will help us if we know exactly the statement we 
> > > have to look for; the result/the error the application saw. It is 
> > > of no use if we just receive the vtrace with the info: my 
> > > application had some time ago (may be in the vtrace or not) some 
> > > trouble with an empty result-row/set.
> > >
> > > But all of us checking your problem believe, that the 
> > > error-handling in your application is not correct. Therefore: 
> > > please check this and let us know, if we can close this thread then.
> > >
> > > Elke
> > > SAP Labs Berlin
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Schroeder, Alexander [mailto:alexander.schroeder@xxxxxxx]
> > > > Sent: Wednesday, 19 January 2005 6:21 PM
> > > > To: Michael Andrewes
> > > > Subject: RE: Row locking problem.
> > > >
> > > > Hello Michael,
> > > > getting back to your original mail ...
> > > >
> > > > > > (one connection)
> > > > > >     1. select " + fieldNames() + " FROM " +
> > > TABLE_NAME + " WHERE
> > > > > > promotion_id = ?    (other properties are used
> > > initially in parts of
> > > > > some
> > > > > > application)
> > > >
> > > > > select " + fieldNames() + " FROM " + TABLE_NAME + " WHERE 
> > > > > property_name = ?
> > > > > and promotion_id = ? FOR UPDATE OF property_value
> > > >
> > > > >
> > > > > >     3.1 select " + fieldNames() + " FROM " +
> > > TABLE_NAME + " WHERE
> > > > > > promotion_id = ?
> > > >
> > > > > >     3.2 update " + TABLE_NAME + " set
> > > property_value = ? where
> > > > > > property_name = ? and promotion_id = ?
> > > >
> > > > > > (commit)
> > > >
> > > > So basically you do:
> > > >
> > > > SELECT
> > > > SELECT FOR UPDATE
> > > > SELECT
> > > > UPDATE
> > > > COMMIT
> > > >
> > > > Second, there are no INSERTS or DELETES here, just UPDATES,
> > > am I right?
> > > >
> > > > Which of the SELECTs gets the 'empty row' and how does this
> > > manifest -
> > > > means empty row that the result set is empty, or do you really 
> > > > mean the value is garbled? (I.e. some fields are there and the
> 'property'
> > > > value you speaking of contains nothing/some garbage/wrong value?
> > > >
> > > > As you say there are only two sessions taking part, you may 
> > > > create possibly simply a VTRACE:
> > > >
> > > > ----------------------------------------------------------
> > > >
> > > > 1) Start the trace: dbmcli -d <dbname> -u <dbm,dbm> util_execute 
> > > > diagnose vtrace default on
> > > >
> > > > 2) Insert into your code in the place where you log that
> > > 'an empty row
> > > > has been found'
> > > >    a statement executing the command "DIAGNOSE VTRACE
> > > DEFAULT OFF", so
> > > > that we can catch
> > > >    the situation.
> > > >
> > > > 3) Flush the trace: dbmcli -d <dbname> -u <dbm,dbm>
> > > >    util_execute diagnose vtrace flush or more simply dbmcli -d 
> > > > <dbname> -u <dbm,dbm> trace_flush
> > > >
> > > > 4) Create the text representation: dbmcli -d <dbname> -u 
> > > > <dbm,dbm> trace_prot akb
> > > >
> > > > 5) Copy the text representation to a local file:
> > > >    dbmgetf -d <dbname> -u <dbm,dbm> -k KNLTRCPRT -f <local
> > > file name>
> > > >
> > > > You may then possibly want to send the trace ...
> > > >
> > > > Regards
> > > > Alexander Schröder
> > > > SAP DB, SAP Labs Berlin
> > > >
> > > >
> > > > --
> > > > MaxDB Discussion Mailing List
> > > > For list archives: http://lists.mysql.com/maxdb To unsubscribe:
> > > http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx
> > >
> > >
> > > --
> > > MaxDB Discussion Mailing List
> > > For list archives: http://lists.mysql.com/maxdb To unsubscribe:
> > > http://lists.mysql.com/maxdb?unsub=mandrewes@xxxxxxxxxxxxxxxxxxx
> > >
> > >
> > >
> > > --
> > > MaxDB Discussion Mailing List
> > > For list archives: http://lists.mysql.com/maxdb To unsubscribe:
> > > http://lists.mysql.com/maxdb?unsub=mandrewes@xxxxxxxxxxxxxxxxxxx
> > >
> > >
> > >


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/maxdb?unsub=mandrewes@xxxxxxxxxxxxxxxxxxx



--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=mailarch@xxxxxxx

<Prev in Thread] Current Thread [Next in Thread>