MySQL Bugs: #11966: MySQL throws native error code : 1205 ,

Bug #11966	MySQL throws native error code : 1205 ,
Submitted:	15 Jul 2005 12:01	Modified:	11 Sep 2005 11:00
Reporter:	Asai N	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	HP/UX (HP-UX 11.i)
Assigned to:		CPU Architecture:	Any

Description:
We are using an enterprise product called Web Delivery that uses MySQL 4.0.20.
We hit a problem with the below error message :

mwserver[18820]: [info] Start processing request (27)
Mon Nov 22 20:35:27 mwserver[18820]: [info] Request submitted by user "postman".
Mon Nov 22 20:36:18 mwserver[18820]: [error] SQLExecDirect has Failed. RC=-1.
Mon Nov 22 20:36:18 mwserver[18820]: [error] Database Error:
Mon Nov 22 20:36:18 mwserver[18820]: [error] INSERT INTO DmsDoc ( DocId, 
DocLoc, DocAuthor, DocName, DocType, DocFormat, DocIsNew, DocDelete, DocOpenMd, DocOpenCt) VALUES ( '', '', '', '', '', 'application/pdf', 1, 0, 0, 0)
Mon Nov 22 20:36:18 mwserver[18820]: [error] SQLSTATE = S1000
Mon Nov 22 20:36:18 mwserver[18820]: [error] NATIVE ERROR = 1205
Mon Nov 22 20:36:18 mwserver[18820]: [error] MSG = [MySQL][ODBC 3.51 Driver]
[mysqld-4.0.20-log]Lock wait timeout exceeded; Try restarting transaction

Our application does not use LOCK table,and we have set autocommit=0 in our product code.
How can this problem be avoided ?

Regds,
- Asai.

How to repeat:
MySQL throws this error when huge number of documents are inserted.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Problem Description :
================
We have an enterprise product called HP Web Delivery that uses MySQL 4.0.20.
One of our customers faces a problem with MySQL used by HP Web Delivery.
MySQL returns the below error, resulting insert failure.

Error Log snippet :
==============
mwserver[18820]: [info] Start processing request (27)
Mon Nov 22 20:35:27 mwserver[18820]: [info] Request submitted by user
"postman".
Mon Nov 22 20:36:18 mwserver[18820]: [error] SQLExecDirect has Failed. RC=-1.
Mon Nov 22 20:36:18 mwserver[18820]: [error] Database Error:
Mon Nov 22 20:36:18 mwserver[18820]: [error] INSERT INTO DmsDoc ( DocId, 
DocLoc, DocAuthor, DocName, DocType, DocFormat, DocIsNew, DocDelete, DocOpenMd,
DocOpenCt) VALUES ( '', '', '', '', '', 'application/pdf', 1, 0, 0, 0)
Mon Nov 22 20:36:18 mwserver[18820]: [error] SQLSTATE = S1000
Mon Nov 22 20:36:18 mwserver[18820]: [error] NATIVE ERROR = 1205
Mon Nov 22 20:36:18 mwserver[18820]: [error] MSG = [MySQL][ODBC 3.51 Driver]
[mysqld-4.0.20-log]Lock wait timeout exceeded; Try restarting transaction

====================

We could find a bug #4852 from bug database that reflects the same MySQL error code #1205 but this deals with lock-tables.
We do not use lock-tables in our application.We feel the bug #4852 might not be relevent to our problem.

We would like to know when MySQL throws the error code 1205, and how this problem could be resolved ?

What kind of inputs do you require to analyse the problem ?

Your early response would be appreciated.

Awaiting your kind reply on this regard.

Regds,
-Asai.

Hi  Miguel Solorzano,

We are ready to provide you the further required information to fix the problem.
Waiting to hear back from you with your valuable comments.

Regds,
- Asai.

Error 1205 in MySQL (see http://dev.mysql.com/doc/mysql/en/error-handling.html) means just what is presented in the message you got: it means that your thread had to wait too long. Some other thread locked the table you are working with.

The only way for your application to execute your statement is to try to execute it later. This can happen in a concurrent environment with any database, not just MySQL. You simply can not do some things from two sessions simultaneously (or data will be corrupted), and locks are just a way to serialize such actions.

Hi,
  This is further update to the information provided by the engineer Asai N.

As from the last reply, MySQL suggested that "The only way for your application to execute your statement is to try to execute it later."  to avoid the NATIVE ERROR.

But we are getting the NATIVE ERROR only for the first simultaneous query from two different session after the restart of MySQL server.  i.e if two similar queries are executed simultaneously by two process after the restart of the MySQL server one of the queries reports NATIVE ERROR = 1205 while the other goes on to complete successfully. Once the query is completed successfully our application COMMIT the database to save the modifications made. 

Now if i do the same operation again i am not getting any NATIVE ERROR and it works fine.

Kindly looking forward for your reply.

Cheers,
Ram

Hi,
  Since i am seeing this for first simultaneous queries only after the restart of the MySQL server and not seeing in the subsequent one, is it something related to start transaction?

FYI: We have done the following in our application 
       SQLSetConnectOption( hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF );