in reply to How do I handle a DB error when a SQL statement must not fail?
It was suggested to me a few days ago that maybe I am encountering a deadlock with two instances of my cgi script trying to insert data at the same time.
No, this likely is not the problem (impossible), you can have 100 of them simultaneous, as long as you can make 100 connections to msyql (mysql configuration stuff), and have enough ram, each and every CGI process should update stuff in the database
Alas, the 0.01% or less of the transactions that fail leave no record of their existence in the MySQL logs, the webserver logs, or logs I create using perl. This is a problem as none of the data involved can be lost,
This suggests network slowdown, where the CGI isn't being reached, very very slowly
Or it suggests a program on the webserver machine (or proxy infront of webserver) is running wild and out of control, eating all the CPU , going to swap , tying up the harddisks and ram and cpu all at once, so nothing else gets a chance to run
Or both things at the same time
The solution to let the web-user know it failed unless you're 100% sure it didn't
on my laptop this happens with windowsupdate sometimes, it always likes to eat cpu (fan kicks in) and read lots of harddisk , and sometimes it goes into infinite loop, then starts swapping memory to disk ... cannot do anyting on laptop, can't even kill windowsupdate, windowsupdate eats all hardware, and I have to reboot
so check your ulimits/proc limits... whatever you're using
if it happens around same time, check your cron jobs and the like (windows update, scheduled tasks ... whatever your os)
so if there is a time-frame/time-window when this happens, pick the hour, then flip on maximum process monitoring/logging and give that procwatcher the maximum priority you can ... and watch to see what happens :)
The way to handle this on the browser side is post-redirect-get ... if the post times out, the user submitting the data will get a timeout
The other way to handle it is post-redirect-get through AJAX ... make sure to notify the user when post-redirect-get fails
It should fail everytime the data isn't inserted in the database
This way there is no chance data goes missing
|
---|
Replies are listed 'Best First'. | |
---|---|
Re^2: How do I handle a DB error when a SQL statement must not fail?
by ted.byers (Monk) on Mar 29, 2014 at 03:41 UTC |