If you are using a MyISAM table type, the entire table is either locked or
it isn't. Normally this isn't a problem most statements happen quickly.
However, yours takes a couple of hours to finish so the entire table has
to stay locked until your statement completes.
The way I see it, you have only a few options:
1) Redesign your update to happen in smaller batches (scripting maybe?).
This gives the engine a chance to handle other requests between the
updates/replaces.
2) Change your update/replace so that it doen't need to work on so many
records. That will help it to finish sooner and not lock you down for so
long.
3) Change to an InnoDB table type. It supports row-level locking.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
matt ryan <matt_lists@stripped> wrote on 08/12/2004 01:59:42 PM:
> SQL is, replace delayed into table c1, c1, c3 select c1, c2, c3 from
> temp table.
>
>
> This takes 2 hours to comlete, the temp table is rather large.
>
> The table being updated is locked, the whole time, all web requests are
> locked and the pages time out.
>
> Is there any way to get this to run without locking the whole table? I
> thought with myisam it would only lock a table if you delete records,
> and insert records, it locks it to fill the gaps.
>
> If I need to switch to another table type it's an option, having locked
> tables is NOT an option.
>
>
> Thanks in advance Matt
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
