FW: Lock timeouts

Forwarded response from Micheal. I haven't tested it yet (have to wait =
till
I have more data to move), but this sounds about right.

Thanks!

-----Original Message-----
From: Michael Dykman [mailto:mdykman@gmail.com]=20
Sent: Thursday, July 09, 2009 1:22 PM
To: Walton Hoops
Subject: Re: Lock timeouts

You have no index for 'Weekend' which means that InnoDB can't just
lock the appropriate rows.. it' does not know which rows will be
affected until it has touched ALL of them. Try creating an index on
'Weekends'.. this should help you avoid those locks.

- michael dykman


On Thu, Jul 9, 2009 at 3:12 PM, Walton Hoops =
wrote:
> 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 =A0`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? =A0It'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: =A0 =
=A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail.com
>
>



--=20
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.


--
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

Re: FW: Lock timeouts

Nope, Nathan is right: INSERT...SELECT is a locking statement due to
statement-based replication. There's lots of info on this if you
google it. The common solution is to dump to disk and load back in
with SELECT INTO OUTFILE and LOAD DATA INFILE.

- Perrin

On Thu, Jul 9, 2009 at 3:55 PM, Walton Hoops wrote:
> Forwarded response from Micheal. =A0I haven't tested it yet (have to wait=
till
> I have more data to move), but this sounds about right.
>
> Thanks!
>
> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@gmail.com]
> Sent: Thursday, July 09, 2009 1:22 PM
> To: Walton Hoops
> Subject: Re: Lock timeouts
>
> You have no index for 'Weekend' which means that InnoDB can't just
> lock the appropriate rows.. =A0it' does not know which rows will be
> affected until it has touched ALL of them. =A0Try creating an index on
> 'Weekends'.. =A0this should help you avoid those locks.
>
> =A0- michael dykman
>
>
> On Thu, Jul 9, 2009 at 3:12 PM, Walton Hoops wrot=
e:
>> 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 =A0`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 kee=
p
>> 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 inser=
t
>> statement from running, but isn't that exactly what InnoDB's row level
>> locking is supposed to prevent? =A0It'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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmai=
l.com
>>
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@gmail.com
>
> =A0- All models are wrong. =A0Some models are useful.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dperrin@elem.c=
om
>
>

--
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