Re: Does writing new records while massive update will generate lock ?

On 08/21/2014 08:41 AM, Victor d'Agostino wrote:

> UPDATE MYBIGTABLE SET date = (SELECT date FROM INDEXEDTABLE WHERE
> INDEXEDTABLE.email_id=MYBIGTABLE.email_id) WHERE date is null;

I may be wrong here, but wouldn't this style of query force a nested
loop? Over several million rows, that would take an extremely long time.
You might want to try this syntax instead:

UPDATE MYBIGTABLE big
    SET date = idx.date
   FROM INDEXEDTABLE idx
  WHERE idx.email_id = big.email_id
    AND big.date IS NULL;

> This transaction is still running and will end in several days. It only
> uses 1 core.

That's not your problem. I suspect if you checked your RAID IO, you'd
see 100% IO utilization because instead of a sequence scan, it's
performing a random seek for every update.

> My question is : Can I add new records in the table or will it generate
> locks ?

Your update statement will only lock the rows being updated. You should
be able to add new rows, but with the IO consuming your RAID, you'll
probably see significant write delays that resemble lock waits.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email