From: Zabach, Elke (elke.zabachsap.com)
Date: Fri Jan 21 2005 - 09:17:49 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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.schroedersap.com]
> 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.zabachsap.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]