Hi, I am still having the problem that sometimes when two transactions try to get and lock a row, one can return an empty row (as if the property is not there), I have been unable to replicated it on our test systems, I assume because they are much slower than production, the time period is usually witin 1/10th of a second. IE TNS 1 2005-01-13 11:49:59.912743 TNS 22005-01-13 11:49:59.948683
At the moment my transaction level is TRANSACTION_READ_COMMITTED, and the query is in the format of "select field1,field2 from table1 where field 1 = ? For update of field 2". Could this be that after one transaction has updated the property and not yet committed, that the second one see's it as uncommitted and ignores it? (if so, this behavior is not what I want). 99% of the time (all the time on test environment), everything works fine and lock behavior is fine. I am hoping that there may be an extra lock suffix I can append to the query, or perhaps I database parameter. Is there any information I can provide that will help in resolving the problem? Regards Michael Andrewes *********** As I said in my email, I went through and noticed that exceptions were not passed all the way to the top level in this method ( this I fixed ). However if any exceptions were thrown, it would have still been logged, but I could never find the exception. This was actually the case from some areas that did not pass in a Database connection, but where I am having the problem, any database exceptions would be passed up to the top level. However I am still getting the same result, I lock or something must be occuring, but it cannot be the application as far as I can tell, as any database errors would now be being passed to the top level, but instead Still the exception I generate if the property value is null is being thrown, which is of course just after getting the result from the DB. *********** Regards Michael Andrewes -----Original Message----- From: Zabach, Elke [mailto:[EMAIL PROTECTED] Sent: Friday, 14 January 2005 12:05 AM To: Michael Andrewes; SAP DB mailing list Subject: AW: Row locking problem. Michael Andrewes wrote: > > Hi, > > I've had a problem with row locking for some time now, and untill now have > been able to work around it. > > I'm running SapDB 7.4.3 Build 010-000-035-462, and while I would like to > upgrade, this server is used for 24/7 > Sms and web applications, so upgrading would cause downtime and > potentially headaches, so I've been avoiding it. > > > Basically, I have a very simple properties table for storing small amounts > mostly non-essential information, in the following format. > | PROMOTION_ID(FIXED) | PROPERTY_NAME(VARCHAR) | PROPERTY_VALUE(VARCHAR) > | > > Primary key is (Promotion_id,propery_name) > > > > The problem I'm having is that sometimes when two or more > clients/transactions request the same row at nearly the same time, one of > them will return an > Empty row, ??? Empty ro returned?? Empty rows will never be returned. We have to assume that an error (lock timeout or the like) occurs and is not handled correctly. Please check this first. > this effects me a lot when I'm doing something like updating a > counter. IE > > | 296 | test | 171 | > > > Here is the processing start/stop times on two recent messages that caused > this problem. > 2005-01-13 11:49:59.912743 | 2005-01-13 11:50:00.050357 > 2005-01-13 11:49:59.948683 | 2005-01-13 11:50:00.20023 > > > > > > The operations on this table when this happens should be as follows. > isolation=TRANSACTION_READ_COMMITTED > > (one connection) > 1. select " + fieldNames() + " FROM " + TABLE_NAME + " WHERE > promotion_id = ? (other properties are used initially in parts of some > application) > 2. select " + fieldNames() + " FROM " + TABLE_NAME + " WHERE > promotion_id = ? for update of property_value > in other mail it was written: Sorry, Step 2 is this 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) > > * 3.1 is used by the applications save method to determine whether to do > an > update or insert. > Sorry, but I am not able to understand what you really want to do. 3.1 is done for decision if insert or update should be done. What about insert...update duplicates? Which select returns 'an empty row'? Do you have a vtrace (after having checked if errors are handled) of this case? Is step 2 necessary (to lock rows) before doing 3.2? Do you need to read the old property_values to be able to calculate the new ones which could be changed to update ... property_value=property_value + ? (then no insert ... update duplicates is possible) You see, depending on your needs, there may be possible changes, but no real answer to your question with the empty row. Your transaction level seems to be ok for this. Elke SAP Labs Berlin > > Has anyone had any similar problems? Or are there other lock options > /isolation levels I could specify? > I've tried a few but I have not been able to replicate this problem in our > test (or even testing on production) environments, so am at a loss, > but it happens in our Production environment between 0 and 2-300 times > per > day. I currently work around it by initialising the property somewhere > else, then if the property is null, I reprocess the SMS message (works, > but > sometimes has a few goes). > > > > > > Michael Andrewes - Software Director > C O M M U N I C A T O R Interactive Marketing > > P +612 9719 1469 F +612 9719 1796 M +61 410 677 458 PAGER +612 9214 8752 > W www.communicator.com.au > Copyright Communicator Interactive Marketing Pty Limited 2004 > > CONFIDENTIAL INFORMATION owned exclusively by Communicator Interactive > Marketing Pty Limited. The information in this email and any attachments > is > privileged and confidential, intended only for the use of the recipient or > the intended recipient. If you are not the intended recipient, any use of > the information is strictly prohibited. If you have received this > communication in error, please inform us immediately and delete the > message and any attachments > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
