Lock timeouts

Oh wise and all knowing MySQL Guru's:

I've been running into a problem recently that has be confused.
I have 2 tables, both with the structure:
DROP TABLE IF EXISTS `acn_market_level`.`market_scans`;
CREATE TABLE `acn_market_level`.`market_scans` (
`Retailer` char(3) NOT NULL,
`Marketkey` int(11) NOT NULL,
`UPCKEY` bigint(20) NOT NULL DEFAULT '0',
`Weekend` int(10) unsigned NOT NULL,
`Dollars` decimal(17,2) DEFAULT NULL,
`Units` bigint(20) unsigned DEFAULT NULL,
`PctAcv` float unsigned DEFAULT NULL,
`SPMD` float unsigned DEFAULT NULL,
`PromoDollars` float unsigned DEFAULT NULL,
PRIMARY KEY (`Retailer`,`Marketkey`,`UPCKEY`,`Weekend`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

(downloads.market_scans is identical)

I have a long running query (ok 10 minutes):

insert into acn_market_level.market_scans
select * from downloads.market_scans
where weekend > 20090613


While this query is running I am also trying to insert rows into
downloads.market_scans, all of them with a weekend < 20090613, but I keep
seeing this error:
Lock wait timeout exceeded; try restarting transaction
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:105 5)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956 )
.....

If I understand this right, the select statement is preventing the insert
statement from running, but isn't that exactly what InnoDB's row level
locking is supposed to prevent? It's not that big a deal, but I feel like
I'm missing something here.

Thanks in advance!
Walton


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Lock timeouts

Walton,

The table in the select is getting locked for the duration of the query bec=
ause it is part of the insert statement. I don't know the technical reason=
for this, but I know it works this way. (having experienced this behavior =
several times myself) The only way (that I am aware of) to avoid that lock=
ing is to break the operation into two steps: 1) store the result of the s=
elect in a file or something 2) load the result into the destination table=
.. Perhaps somebody else knows of a better solution...


Regards,
Nathan

-----Original Message-----
From: Walton Hoops [mailto:walton@vyper.hopto.org]=20
Sent: Thursday, July 09, 2009 2:12 PM
To: mysql@lists.mysql.com
Subject: Lock timeouts

Oh wise and all knowing MySQL Guru's:

I've been running into a problem recently that has be confused.
I have 2 tables, both with the structure:
DROP TABLE IF EXISTS `acn_market_level`.`market_scans`;
CREATE TABLE `acn_market_level`.`market_scans` (
`Retailer` char(3) NOT NULL,
`Marketkey` int(11) NOT NULL,
`UPCKEY` bigint(20) NOT NULL DEFAULT '0',
`Weekend` int(10) unsigned NOT NULL,
`Dollars` decimal(17,2) DEFAULT NULL,
`Units` bigint(20) unsigned DEFAULT NULL,
`PctAcv` float unsigned DEFAULT NULL,
`SPMD` float unsigned DEFAULT NULL,
`PromoDollars` float unsigned DEFAULT NULL,
PRIMARY KEY (`Retailer`,`Marketkey`,`UPCKEY`,`Weekend`)
)ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 ROW_FORMAT=3DCOMPACT;

(downloads.market_scans is identical)

I have a long running query (ok 10 minutes):

insert into acn_market_level.market_scans
select * from downloads.market_scans
where weekend > 20090613


While this query is running I am also trying to insert rows into
downloads.market_scans, all of them with a weekend < 20090613, but I keep
seeing this error:
Lock wait timeout exceeded; try restarting transaction
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:105 5)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956 )
.....

If I understand this right, the select statement is preventing the insert
statement from running, but isn't that exactly what InnoDB's row level
locking is supposed to prevent? It's not that big a deal, but I feel like
I'm missing something here.

Thanks in advance!
Walton


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dnsullivan@cappex.co=
m


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg