Bug #5368 conn1 DELETE and conn2 UPDATE on different rows results in timeout with InnoDB

• Submitted: 2 Sep 2004 15:30
• Modified: 2 Sep 2004 18:46
• Reporter: Hakan Küçükyılmaz
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.0
• OS: Linux (Linux SuSE9.1)
• CPU Architecture: Any

[2 Sep 2004 15:30] Hakan Küçükyılmaz

Description:
conn1 INSERT and conn2 UPDATE on different rows on same table results in timeout.

How to repeat:
###################### ps_ver03621.test ######################
#                                                            #
#  test for Veri VER03621                                    #
#                                                            #
##############################################################

USE test;

SET GLOBAL sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES";
SET GLOBAL init_connect='SET AUTOCOMMIT=0';

SET SESSION sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES";
# SET SESSION init_connect='SET AUTOCOMMIT=0';

--disable_warnings
DROP TABLE IF EXISTS VERI_CLNT;
--enable_warnings

let $type= 'InnoDB';

# Create table VERI_CLNT
eval CREATE TABLE "VERI_CLNT" (
  "CLIENT" char(3) character set latin1 collate latin1_bin NOT NULL default '000',
  "ARG1" char(3) character set latin1 collate latin1_bin NOT NULL default '',
  "ARG2" char(3) character set latin1 collate latin1_bin NOT NULL default '',
  "FUNCTION" varchar(10) character set latin1 collate latin1_bin NOT NULL default '',
  "FUNCTINT" int(11) NOT NULL default '0',
  PRIMARY KEY  ("CLIENT","ARG1","ARG2"),
  KEY "VERI_CLNT~1" ("ARG1","FUNCTION","FUNCTINT"),
  KEY "VERI_CLNT~2" ("ARG1","ARG2")
) ENGINE = $type DEFAULT CHARSET=latin1;

BEGIN;

#  PREPARE stmt_name
PREPARE stmt FROM 'INSERT INTO `VERI_CLNT` 
                   ( `CLIENT` ,`ARG1` ,`ARG2` ,`FUNCTION` ,`FUNCTINT` ) 
                   VALUES( ? , ? , ? , ? , ? )';

# SET vars
SET @var1 = '000';
SET @var2 = ' 0';
SET @var3 = ' 0';
SET @var4 = 'Text 001';
SET @var5 = 0;

#  EXECUTE stmt_name
EXECUTE stmt USING @var1, @var2, @var3, @var4, @var5;

# SET vars
SET @var1 = '000';
SET @var2 = ' 1';
SET @var3 = ' 0';
SET @var4 = 'Text 005';
SET @var5 = 1;

#  EXECUTE stmt_name
EXECUTE stmt USING @var1, @var2, @var3, @var4, @var5;

# DEALLOCATE PREPARE stmt_name;
DEALLOCATE PREPARE stmt ;

COMMIT;

# BEGIN Operations
# Connection default
BEGIN;

PREPARE stmt FROM 'DELETE FROM `VERI_CLNT` WHERE `CLIENT` = ? AND `ARG1` = ?';

# SET vars
SET @var1 = '000';
SET @var2 = ' 0';

#  EXECUTE stmt_name
EXECUTE stmt USING @var1, @var2;

# DEALLOCATE PREPARE stmt_name;
DEALLOCATE PREPARE stmt ;

# No commit here!

# Connection 2
# New Connection
connect (conn2, localhost, root, ,test);
connection conn2;

BEGIN;

PREPARE stmt FROM 'UPDATE `VERI_CLNT` SET `FUNCTION` = ? WHERE `CLIENT` = ? AND `ARG1` = ?';

# SET vars
SET @var1 = 'error';
SET @var2 = '000';
SET @var3 = ' 1';

#  EXECUTE stmt_name
EXECUTE stmt USING @var1, @var2, @var3;

DEALLOCATE PREPARE stmt ;

COMMIT;

# Clean up
DROP TABLE VERI_CLNT;

[2 Sep 2004 18:46] Hakan Küçükyılmaz

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php