Waiting for metadata lock on 'table', when running insert/ when duplicate update

Help Procedural SQL
1

We are running an insert/when duplicate update over a rowstore table, it has in total 2M rows. We tried updating into batches of 100k, 10k, 4k, 1k (with start transaction as well), for all the cases it reaches a point on which the rest of the queries that are being done into the same table are locked with the message “waiting for metadata lock on ‘table’” and it got stuck for a minute until all the updates finish.

Do you know why this is doing a metadata lock? Is there a way to do this update without locking the whole table?

Memsql version 7.0.16.

2

I don’t have a direct answer for you. However, see

SELECT * FROM information_schema.MV_BLOCKED_QUERIES;

to learn what’s waiting for what.

I’d suggest trying smaller batches, like 200 instead of 1000.

7.0 was end of life December 09, 2021. You really should upgrade.

3

I checked with one of our dev experts, and he said:

" I think waiting for metadata lock on can only happen if they are running some kind of ddl (or very least compiling new queries)

so I don’t think it should happen if they only run queries as described"

If you have different batch sizes (different number of literal rows in your INSERT ON DUP KEY UPDATE commands) it can cause a compile. So make sure you have only a few standard sizes, like only 1 row and 200 rows. That will limit compiles. And make sure no DDL is going on (no ALTERs or CREATE TABLE etc.).

4

Thanks @hanson I also tried changing the batch size but still the same problem. Anyway I found something insteresting. Right before the metadata lock I’m seeing this running over the table with the problem:

USING schema_name SELECT ORDERED_SERIALIZE(is_value), …
FROM table_that_I'm_updating WITH (sample_ratio = 0.719020464041) OPTION(INTERPRETER_MODE=INTERPRET_FIRST)
/stats_collection_query/

Could it be due to the stats collection? I updating almost every row on the table. If this is the reason, is there a way to avoid it?

5

Let me check. Meanwhile, did you switch to a newer version?

6

That query is due to stats collection; it is internally generated.

But we don’t have an answer for you at this point. The same issue was raised – it appears that some connection is doing DDL. If that is not the case, I don’t have a good explanation.

7

Found it! It was that @hanson. Thanks for your help.
I resolved it by setting: ALTER TABLE mylockedtable AUTOSTATS_ENABLED = OFF;

Looks like we are updating most of the rows on the table and the optimizer “thinks” that we added a lot of rows to that table (But we know is not like that).
Then the optimizer invalidates all the plans starts collecting the stats of the table, and puts all new queries on hold until it has the statistics to create the execution plan.

I will have to collect the stats manually every few weeks, but it’s not a problem. All my locks disappeared after this change and it’s working perfectly.

1 Like
8

@moralez.rodrigo Thanks so much for posting your solution!