Description:
Using Client A and Client B. Client A causes a locktimeout in Client B. Client B's compound key is however different from ClientA's compound key.
The COMMIT of CLIENT A can be executed long after the COMMIT of CLIENT B has been executed, due to historical reasons of our applications. Time between start transaction and commit can be minutes. The default lock timeout of 50 seconds will ocure. PostgreSQL doesn't have this problem
How to repeat:
TEST PROCEDURE :
1) START SQL YOG.
2) execute : "possible MySQL4.0.21 bug DATABASE setup.sql"
with SHIFT F5
3) CLOSE SQL YOG.
4) Start 2 SQL YOG SESSIONS (A) & (B)
5) Execute in (A) "possible MySQL4.0.21 bug CLIENT A.sql".
with SHIFT F5
6) Execute in (B) "possible MySQL4.0.21 bug CLIENT B.sql" with
SHIFT F5
------------------------------------------------
possible MySQL4.0.21 bug DATABASE setup.sql
------------------------------------------------
USE kango;
SET AUTOCOMMIT=1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DROP DATABASE if exists `kango`;
CREATE DATABASE if not exists `kango`;
USE kango;
SET AUTOCOMMIT=1;
CREATE TABLE if not exists `opdrachten` (
`import_baan_handmatig` char( 1) BINARY NOT NULL,
`opdracht_nr` varchar( 12) BINARY NOT NULL,
`opdracht_positie` varchar( 12) BINARY NOT NULL,
`opdracht_positie_volgnr` varchar( 5) BINARY NOT NULL,
`a_nummer` varchar( 12) BINARY NOT NULL,
`a_nummer_volgnr` varchar( 5) BINARY NOT NULL,
`status` char( 1) BsINARY NOT NULL,
`datum_invoer` varchar( 10) default NULL,
`database_versie_initiele_invoer` varchar( 10) default NULL,
`xml_certdat_versie_initiele_invoer` varchar( 10) default NULL,
`ijk_datum` varchar( 10) default NULL,
`import_error` varchar( 160) default NULL,
`locked` char( 1) default NULL,
PRIMARY KEY (`import_baan_handmatig`, `opdracht_nr`, `opdracht_positie`, `opdracht_positie_volgnr`, `a_nummer`, `a_nummer_volgnr`),
KEY `zonder_source` (`opdracht_nr`, `opdracht_positie`, `opdracht_positie_volgnr`, `a_nummer`, `a_nummer_volgnr`),
KEY `a_nummer` (`a_nummer`, `a_nummer_volgnr`),
KEY `status` (`status`),
KEY `ijkdatum` (`ijk_datum`)
) TYPE=InnoDB;
CREATE TABLE if not exists `opdracht_eigenschappen_blob_waarden` (
`import_baan_handmatig` char( 1) BINARY NOT NULL,
`opdracht_nr` varchar( 12) BINARY NOT NULL,
`opdracht_positie` varchar( 12) BINARY NOT NULL,
`opdracht_positie_volgnr` varchar( 5) BINARY NOT NULL,
`a_nummer` varchar( 12) BINARY NOT NULL,
`a_nummer_volgnr` varchar( 5) BINARY NOT NULL,
`eigenschap_naam_referentie` varchar( 20) BINARY NOT NULL,
`unicode` char( 1) binary NOT NULL,
`waarde` blob,
PRIMARY KEY (`import_baan_handmatig`, `opdracht_nr`, `opdracht_positie`, `opdracht_positie_volgnr`, `a_nummer`, `a_nummer_volgnr`, `eigenschap_naam_referentie`)
) TYPE=InnoDB;
CREATE TABLE if not exists `opdracht_eigenschappen_vaste_waarden` (
`import_baan_handmatig` char( 1) BINARY NOT NULL,
`opdracht_nr` varchar( 12) BINARY NOT NULL,
`opdracht_positie` varchar( 12) BINARY NOT NULL,
`opdracht_positie_volgnr` varchar( 5) BINARY NOT NULL,
`a_nummer` varchar( 12) BINARY NOT NULL,
`a_nummer_volgnr` varchar( 5) BINARY NOT NULL,
`eigenschap_naam_referentie` varchar( 20) BINARY NOT NULL,
`indx` varchar( 12) BINARY,
`special_indicatie` char(1) default '',
PRIMARY KEY (`import_baan_handmatig`, `opdracht_nr`, `opdracht_positie`, `opdracht_positie_volgnr`, `a_nummer`, `a_nummer_volgnr`, `eigenschap_naam_referentie`),
INDEX(`eigenschap_naam_referentie`, `indx`)
) TYPE=InnoDB;
CREATE TABLE if not exists `opdracht_eigenschappen_variabele_waarden` (
`import_baan_handmatig` char( 1) BINARY NOT NULL,
`opdracht_nr` varchar( 12) BINARY NOT NULL,
`opdracht_positie` varchar( 12) BINARY NOT NULL,
`opdracht_positie_volgnr` varchar( 5) BINARY NOT NULL,
`a_nummer` varchar( 12) BINARY NOT NULL,
`a_nummer_volgnr` varchar( 5) BINARY NOT NULL,
`eigenschap_naam_referentie` varchar( 20) BINARY NOT NULL,
`unicode` char( 1) BINARY NOT NULL,
`waarde_tekst` varchar( 200) default NULL,
PRIMARY KEY (`import_baan_handmatig`, `opdracht_nr`, `opdracht_positie`, `opdracht_positie_volgnr`, `a_nummer`, `a_nummer_volgnr`, `eigenschap_naam_referentie`),
INDEX(`eigenschap_naam_referentie`, `waarde_tekst`)
) TYPE=InnoDB;
CREATE TABLE if not exists `eigenschap_namen` (
`eigenschap_naam_referentie` varchar( 20) BINARY NOT NULL,
`eigenschap_naam` varchar( 50) default NULL,
`eigenschap_soort` varchar( 50) default NULL,
`long_reference` varchar( 30) default NULL,
PRIMARY KEY (`eigenschap_naam_referentie`)
) TYPE=InnoDB;
CREATE TABLE if not exists `eigenschap_waarden` (
`eigenschap_naam_referentie` varchar( 20) BINARY NOT NULL,
`tekst_type_referentie` varchar( 12) BINARY NOT NULL,
`taal_naam_referentie` char( 3) BINARY NOT NULL,
`indx` varchar( 12) BINARY NOT NULL,
`waarde_tekst` varchar( 200) default NULL,
`unicode` char(1) BINARY NOT NULL,
PRIMARY KEY (`eigenschap_naam_referentie`, `tekst_type_referentie`, `taal_naam_referentie`, `indx`)
) TYPE=InnoDB;
CREATE TABLE if not exists `tekst_type_namen` (
`tekst_type_referentie` varchar( 12) BINARY NOT NULL,
`tekst_type_naam` varchar( 50) default NULL,
PRIMARY KEY (`tekst_type_referentie`)
) TYPE=InnoDB;
CREATE TABLE if not exists `taal_namen` (
`taal_naam_referentie` char( 3) BINARY NOT NULL,
`taal_naam` varchar( 50) default NULL,
PRIMARY KEY (`taal_naam_referentie`)
) TYPE=InnoDB;
CREATE TABLE if not exists `locked` ( `opdracht_nr` varchar(12) binary NOT NULL default '', `opdracht_positie` varchar(12) binary NOT NULL default '', `opdracht_positie_volgnr` varchar(5) binary NOT NULL default '', `a_nummer` varchar(12) binary NOT NULL default '', `a_nummer_volgnr` varchar(5) binary NOT NULL default '', `lockedStatus` varchar(60) binary NOT NULL default '', PRIMARY KEY (`opdracht_nr`,`opdracht_positie`,`opdracht_positie_volgnr`, `a_nummer`,`a_nummer_volgnr`) ) TYPE=InnoDB;
----------------------------------------------------------------------
possible MySQL4.0.21 bug CLIENT A.sql
----------------------------------------------------------------------
/* CLIENT A */
USE kango;
SET AUTOCOMMIT=1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;
/* IT'S THE UPDATE STATEMENT BELOW THAT CAUSES CLIENT B TO GET A LOCK TIMEOUT. ALTHOUGH THE KEY VALUES OF CLIENT B ARE
DIFFERENT FROM THAT OF CLIENT A. THE STANGE THING EVEN IS THAT IN THE STATEMENT BELOW, NOTHING IS CHANGED IN THE TABLE */
UPDATE
opdracht_eigenschappen_vaste_waarden
SET
a_nummer = '---' ,
a_nummer_volgnr = '---'
WHERE
import_baan_handmatig= '1' And
opdracht_nr= '400020' And
opdracht_positie= '10' And
opdracht_positie_volgnr= '1' And
a_nummer = '---' And
a_nummer_volgnr = '---';
----------------------------------------------------------------------
possible MySQL4.0.21 bug CLIENT B.sql
----------------------------------------------------------------------
USE kango;
SET AUTOCOMMIT=1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;
INSERT INTO
opdracht_eigenschappen_vaste_waarden
VALUES (
'1' ,
'400020' ,
'10' ,
'2' ,
'---' ,
'---' ,
'EA',
'10',
'0'
);
/*
Error Code : 1205
Lock wait timeout exceeded; Try restarting transaction
(51547 ms taken)
*/
Suggested fix:
PostgreSQL has no problem with these kind of SQL statements. I guess that MySQL/InnoDB has an error. If InnoDB is truelly MVCC, Client B should not be locked but should be abble to execute the SQL statements directly.
We want to use MySQL because PosgreSQL has no replication support on Windows systems.
