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

In reply to Re: How do I handle a DB error when a SQL statement must not fail? by Anonymous Monk
in thread How do I handle a DB error when a SQL statement must not fail? by ted.byers

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.