asked
Data field not updated Occasionally on one machine
Hi everybody, I am having a rather peculiar problem and hope you can shed some light on it. This is an in-house developed solution and has been running for more than 10 years. Recently I found that there is one user (or maybe one machine) having the problem of updating a particular column every now and then. I've done some investigation and following is my finds: 1) when the problem occurred (ie, the change to this particular field was not saved) the changes to other fields of the record were saved. 2) the subsequent tries normally work. 3) the error ratio is about 1/5. This is a frequently used table accessed by multiple users at any given time. There has never been such problem reported in other data columns of the table. The fact that the same code works fine on other computers (and, it happens on this computer occasionally) indicates the data saving logic is correct. It looks like a machine related issue, but, other aspects of the WS (other applications, and other functions of the same application) works fine...I have run out of ideas on how to troubleshoot this problem! Thank you all in advance for your help!
Thank you for the response, John! Here are the answers to your questions: 1) the code that updates the column: UPDATE SET BillToAddr1 = '', BillToAddr2 = '1321 Ketch Court', BillToCity = 'Coquitlam', BillToCountry = 'CAN', BillingNote = 'Testing 101' (this is the field in question), ModifiedBy = 'YvonneZ', ModifiedDate = '11/06/2017', ModifiedTime = '13:32:07' .WHERE SONum = '175337' I've observed a couple of times when the error happened (the BillingNote field was not updated) the other fields, such as ModifiedBy, ModifiedDate were updated. 2) Can someone update the field from behind the scenes outside of the normal process? No. If others want to update the field they will have to use the same procedure. 3) Can you repeat the problem? It happens randomly but I was able to repeat the problem, which is how I came up the ratio of error is about 1/5. When it happened I queried the table and the field (BillingNote) was empty. But, this error only happens (for what we know) in this machine. The other users do not have such problem and I cannot repeat the problem in my test/dev environment. Again, thank you for your time and your brain-power! Yvonne
Are there any triggers on the table? Is there anything else that updates that table?
Thanks for the questions, Thomas! No, there is no any triggers on that table. This is a rather old-fashioned application in which most of the data process logics are done in the code rather than in the DB. There is an application level lock on the table allowing only one user to update/Read the table at a time. So, if somebody else updates the table, it has to be after this user releases the lock. Thanks again! Yvonne
18 People are following this question.
