List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 8 2002 11:40pm
Subject:RE: Simultaneous SELECT and UPDATE on the same table
View as plain text  
At 21:14 +0100 3/8/02, Roger Baklund wrote:
>* Paul DuBois
>>  At 17:00 +0000 3/8/02, Corin Hartland-Swann wrote:
>[...]
>>  >This works for a varying number of rows (usually 5,000 to 10,000) before
>>  >it silently exits the loop. I'm using MySQL 3.23.49a
>[...]
>>
>>  That's also your problem.  With mysql_use_result, it's *required*
>>  that you completely finish the query before issuing another one.
>>
>>  I suppose you could write the IDs to a file, then read them back in
>>  and use them to issue the UPDATE statements.
>
>Why not make two connections, SELECT from one and UPDATE through the other?

I thought of that, too, but I don't think it would work.  The SELECT locks
the table until it's done.  That will lock out the UPDATE, which needs a
WRITE lock no matter which connection it's done over.

Of course, if anyone tries it and find that it works, I'd be interested
to know that.

>
>--
>Roger
Thread
Simultaneous SELECT and UPDATE on the same tableCorin Hartland-Swann8 Mar
  • ETA for MySql having sub queries capabilitiesRolando Morales8 Mar
    • Re: ETA for MySql having sub queries capabilitiesMatt Wagner8 Mar
Re: Simultaneous SELECT and UPDATE on the same tablePaul DuBois8 Mar
  • RE: Simultaneous SELECT and UPDATE on the same tableRoger Baklund8 Mar
    • RE: Simultaneous SELECT and UPDATE on the same tablePaul DuBois9 Mar
      • RE: Simultaneous SELECT and UPDATE on the same tableCorin Hartland-Swann9 Mar