MySQL Bugs: #1728: lock timeout not consistently returned by mysql

Bug #1728	lock timeout not consistently returned by mysql_query()
Submitted:	31 Oct 2003 17:01	Modified:	13 Dec 2003 4:44
Reporter:	Kerwin Holmankus	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Linux (Red Hat linux 8.0)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
mysql_query() does not consistently return a non-zero value
when a lock timeout occurs. In some instances the error is 
not detected until mysql_store_result() is subsequently 
called and returns a NULL value. The retrieved error code 
then correctly indicates a lock timeout.

I have done some prelimary testing to obtain more information
about this error.

The "test" table I am using is shown below:

CREATE TABLE temp_ad_info_table (
  login_name char(8) NOT NULL default '',
  ad_id int(10) unsigned NOT NULL default '0',
  ad_recvd_date datetime NOT NULL default '0000-00-00 00:00:00',
  title char(15) NOT NULL default '',
  first_name char(25) NOT NULL default '',
  last_name char(40) NOT NULL default '',
  street_address char(250) NOT NULL default '',
  city char(28) NOT NULL default '',
  prov_state char(30) NOT NULL default '',
  is_other_prov_state enum('N','Y') NOT NULL default 'N',
  country char(35) NOT NULL default '',
  is_other_country enum('N','Y') NOT NULL default 'N',
  post_code char(15) NOT NULL default '',
  area_code char(6) NOT NULL default '',
  phone char(10) NOT NULL default '',
  ph_ext char(5) NOT NULL default '',
  fax_num char(15) NOT NULL default '',
  e_mail char(40) NOT NULL default '',
  website char(60) NOT NULL default '',
  city_cme char(28) NOT NULL default '',
  prov_state_cme char(30) NOT NULL default '',
  is_other_prov_state_cme enum('N','Y') NOT NULL default 'N',
  country_cme char(35) NOT NULL default '',
  is_other_country_cme enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (ad_id),
  KEY log_idx (login_name),
  KEY locperm_idx (ad_type),
  KEY prac_type_idx (prac_type),
  KEY subspec_idx (subspecialty),
  KEY city_idx (city),
  KEY provstate_idx (prov_state),
  KEY ad_recdate_idx (ad_recvd_date)
) TYPE=InnoDB;

The small test program I am using to produce the 
lock timeout is shown below:

#include <string.h>
#include <mysql/mysql.h>
#include <mysql/mysqld_error.h>
#include <stdio.h>
#include <unistd.h>

#define db_host_name NULL
#define db_user_name "advert"
#define db_user_passwd "abcdef"
#define db_to_use "advert_db4"

int main(int argc, char* argv[])
{
int num_ads;
unsigned mysql_error_num;
char QRY_buffer1[256];
MYSQL* db_connect_ptr = NULL;
MYSQL* db_connect_server_ptr = NULL; 
MYSQL_RES* QRY_resultset_ptr = NULL;
MYSQL_ROW QRYrow_ptr = NULL;

db_connect_ptr = mysql_init(NULL);

db_connect_server_ptr = mysql_real_connect( db_connect_ptr,
      db_host_name, db_user_name, db_user_passwd, db_to_use,
      0, NULL, 0);
      
strcpy(QRY_buffer1, "SELECT COUNT(*) FROM temp_ad_info_table \
WHERE area_code=\'112\' LOCK IN SHARE MODE"); /* change target 
column during testing */

if(mysql_query(db_connect_ptr, QRY_buffer1) != 0) 
    printf("db error:  %s\n\n", (char*)mysql_error(db_connect_ptr));
    
else
    {
    QRY_resultset_ptr = mysql_store_result(db_connect_ptr);

    if(QRY_resultset_ptr == NULL)
        {
        mysql_error_num = mysql_errno(db_connect_ptr);
	if(mysql_error_num == ER_LOCK_WAIT_TIMEOUT) 
	    puts("lock timeout");
				
        else if(mysql_field_count(db_connect_ptr) != 0) 
	    printf("db error: %s\n\n", (char*)mysql_error(
	    db_connect_ptr));
        }

    else /*SELECT query alloc'd result_struct normally */
        {
        QRYrow_ptr = mysql_fetch_row(QRY_resultset_ptr);
        
	if(QRYrow_ptr == NULL) printf("db error: %s\n\n", 
	    (char*)mysql_error(db_connect_ptr));
       
    	else num_ads = atoi(QRYrow_ptr[0]);
	mysql_free_result(QRY_resultset_ptr);
	}/* end successful res-set alloc block */
    }/* end successful QRY */

mysql_close(db_connect_ptr);
return 0;
}

Test platform:

MySQL 4.0.16 (binary rpm downloaded from Univ 
    Wisconsin -- md5 hash O.K.)
Red Hat Linux 8.0 (stock kernel 2.4.18)
Duron 800 MHz CPU / 512 megs ddr RAM

Test scenario:

Using a terminal window, I do a "BEGIN", followed 
by "SELECT * from temp_ad_info_table WHERE ad_id=14 
FOR UPDATE" to put an exclusive lock on that row 
while I test the above code.

Next, using the DDD debugger, I step through the 
code shown above. The program will correctly halt at 
the "mysql_query(...)" line because of the exclusive 
row lock placed by the above transaction.

However, when the SHARE LOCK timeout occurs, 
mysql_query() will not consistently return a non-zero 
value -- if the target column in the "WHERE" clause has 
an primary key on it, mysql_query() will correctly
return a non-zero result (the lock timeout error number) 
but in other instances, when I either drop the primary 
key from the target column or use a different column 
eg. "area_code" which does not have an primary key, then
mysql_query() incorrectly returns 0 and only when 
mysql_store_result() is called and NULL returned, 
is the lock timeout error code retrievable.

I have done this a number of times using target 
columns (in the "WHERE" clause) that either have or don't 
have a primary key on them and the failure of mysql_query() 
to return a non-zero value for the lock timeout
occurs when the column doesn't have a primary key; 
even if the column has an index, the incorrect 
mysql_query() behaviour still occurs.

I have not yet mysql_query() using a 
deadlock situation.

In any case, I thought that I should let you 
know about this. If one wants to catch lock timeouts 
and handle them gracefully for a user eg. recreate a web 
page with a form so the user can re-submit it, then
it would be helpful to know that mysql_query() should 
catch all lock timeouts.

Ken Holervich

How to repeat:
see above

This is something that can't be fixed (becasue of how MySQL works).

mysql_query() returns as soon as the server have sent the header information (field descriptions) to the client (which happens either when the communication buffer on the server is full or the query ends).
mysql_query() will not wait for the whole query to be executed as this would require the server to create buffer all results until the query ends instead of sending them to the client as the result rows are generated.  In other words, most queries would be much slower to execute.

The few cases where the server may catch the lock early if the lock timeout happens during the optimizing part of the query but this is only true for a small fraction of all queries.

In your case you should regard a query to be ok when you have returned both from mysql_query() and read all rows to the client without any errors (for example with mysql_store_result()).