Actually, the code I showed was a test script designed to test the ability of Exception::Class::TryCatch to catch deadlocks (I have a whole suite of similar tests that verify it's ability to catch more mundane SQL errors, focussed on the construction of the SQL statement itself). It works with a nearly identical script that accesses the same table, but does different updates on the table. The only purpose of the SLEEP(10) is to give me a chance of executing the second test script before the first completes. I can guarantee that there is no SLEEP(10) statement anywhere in my production code. The applicability of the example script I showed relates too the fact I use a try/catch block for the transactions in question. The only difference in the try block is that I have two SQL statements, and autocommit is on (so no need for begin/commit), The second aspect of its applicability is how I ought to restructure that try/catch logic so that, if the first attempt to insert fails, it can be retried either a limited number of times before logging the fact and relevant details, or it succeeds. Remember, this is part of a CGI program, and one of the constraints I impose on my code is that it must respond within 5 to 10 seconds, from the moment the client machine submits the request, to the time it receives my response (my competitor typically take 20 seconds to respond).
Recall, I had asked how to refactor from a simple try/catch logic to a try/catch and retry logic, where retries may have a limit of 5 or 10 before logging the failure). I also don't know, at this stage, whether or not DBI issues some other error if mysql just takes to long to respond, but without mysql generating an exception, or if DBI can produce other exceptions unrelated to the correctness of the SQL submitted. How do I identify the error conditions that DBI may produce that are unrelated to the correctness of the SQL that has been submitted, and what is the best way to respond (server side only) in such a way as to ensure data is never lost?