Thread: Deadlock between concurrent index builds on different tables
I am attempting to build several indexes in parallel, guaranteeing that I never build one on the same table twice. I understand I can't build two on the same table at once or I will get a deadlock. However, I am also getting a deadlock when doing several in parallel on different tables.
Here is an example of the error I am getting:
2017-12-22 15:48:07.669 CST,"CREATE INDEX",2017-12-22 15:48:02 CST,8/32,0,ERROR,40P01,"deadlock detected","Process 4470 waits for ShareLock on virtual transaction 4/262; blocked by process 4466.
Process 4466 waits for ShareLock on virtual transaction 8/32; blocked by process 4470.
Process 4470: CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);
Process 4466: CREATE INDEX CONCURRENTLY index_bar_on_id ON bar USING btree (id);","See server log for query details.",,,,"CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);",,,""
Here is my process:
- Kick off one index build and background 1 second apart, using a queue table to determine what indexes to build and what is finished
- When I determine if a index can be built, I first check if there is another already in build on target table before starting another
- After the index is built in each workflow, I mark it as built in a queue table
I assume that one of the indexes (the earlier transaction) is building and taking so long that several finished indexes end up waiting, and there is perhaps a limit to that?
Any insight appreciated.
Thanks,
Jeremy
> On 22 Dec 2017, at 22:53, Jeremy Finzel <finzelj@gmail.com> wrote: > > 2017-12-22 15:48:07.669 CST,"CREATE INDEX",2017-12-22 15:48:02 CST,8/32,0,ERROR,40P01,"deadlock detected","Process 4470waits for ShareLock on virtual transaction 4/262; blocked by process 4466. > Process 4466 waits for ShareLock on virtual transaction 8/32; blocked by process 4470. > Process 4470: CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at); > Process 4466: CREATE INDEX CONCURRENTLY index_bar_on_id ON bar USING btree (id);","See server log for query details.",,,,"CREATEINDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);",,,"" If I read these logs correctly, you are creating the exact same index on foo (created_at) in both processes, which is justwhat you were trying to prevent doing. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
El 23/12/17 a las 08:08, Alban Hertroys escribió: > >> On 22 Dec 2017, at 22:53, Jeremy Finzel <finzelj@gmail.com> wrote: >> >> 2017-12-22 15:48:07.669 CST,"CREATE INDEX",2017-12-22 15:48:02 CST,8/32,0,ERROR,40P01,"deadlock detected","Process 4470waits for ShareLock on virtual transaction 4/262; blocked by process 4466. >> Process 4466 waits for ShareLock on virtual transaction 8/32; blocked by process 4470. >> Process 4470: CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at); >> Process 4466: CREATE INDEX CONCURRENTLY index_bar_on_id ON bar USING btree (id);","See server log for query details.",,,,"CREATEINDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);",,,"" > > If I read these logs correctly, you are creating the exact same index on foo (created_at) in both processes, which is justwhat you were trying to prevent doing. They are two distinct indexes. One on foo(create_at) and the other on bar(id) -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
El 22/12/17 a las 18:53, Jeremy Finzel escribió: > I am attempting to build several indexes in parallel, guaranteeing that > I never build one on the same table twice. I understand I can't build > two on the same table at once or I will get a deadlock. However, I am > also getting a deadlock when doing several in parallel on different tables. On the same table you wouldn't get a dead lock as CREATE INDEX CONCURRENTLY takes a "share update exclusive lock" which is incompatible with vacuum and schema changes (and stronger locks), and a second CREATE INDEX CONCURRENTLY would be another schema change. So the first one would lock the second, which will have to wait until completion of the locking statement. > Here is an example of the error I am getting: > > 2017-12-22 15:48:07.669 CST,"CREATE INDEX",2017-12-22 15:48:02 > CST,8/32,0,ERROR,40P01,"deadlock detected","Process 4470 waits for > ShareLock on virtual transaction 4/262; blocked by process 4466. > Process 4466 waits for ShareLock on virtual transaction 8/32; blocked by > process 4470. > Process 4470: CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo > USING btree (created_at); > Process 4466: CREATE INDEX CONCURRENTLY index_bar_on_id ON bar USING > btree (id);","See server log for query details.",,,,"CREATE INDEX > CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);",,,"" The only thing I can think of is that there's a foreign key from foo to bar(id), but the create index on bar shouldn't prevent a share lock on foo, even if such a restriction exists. -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Dec 22, 2017 at 1:53 PM, Jeremy Finzel <finzelj@gmail.com> wrote:
Cheers,
I am attempting to build several indexes in parallel, guaranteeing that I never build one on the same table twice. I understand I can't build two on the same table at once or I will get a deadlock. However, I am also getting a deadlock when doing several in parallel on different tables.Here is an example of the error I am getting:2017-12-22 15:48:07.669 CST,"CREATE INDEX",2017-12-22 15:48:02 CST,8/32,0,ERROR,40P01,"deadlock detected","Process 4470 waits for ShareLock on virtual transaction 4/262; blocked by process 4466. Process 4466 waits for ShareLock on virtual transaction 8/32; blocked by process 4470.Process 4470: CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);Process 4466: CREATE INDEX CONCURRENTLY index_bar_on_id ON bar USING btree (id);","See server log for query details.",,,,"CREATE INDEX CONCURRENTLY index_foo_on_created_at ON foo USING btree (created_at);",,,""Here is my process:
- Kick off one index build and background 1 second apart, using a queue table to determine what indexes to build and what is finished
- When I determine if a index can be built, I first check if there is another already in build on target table before starting another
- After the index is built in each workflow, I mark it as built in a queue table
I assume that one of the indexes (the earlier transaction) is building and taking so long that several finished indexes end up waiting, and there is perhaps a limit to that?
Each index build needs to wait for all other transactions (Including the ones used by the other index build) to finish. So I don't think a deadlock here is unexpected.
Jeff
Each index build needs to wait for all other transactions (Including the ones used by the other index build) to finish. So I don't think a deadlock here is unexpected.Cheers,Jeff
Does that mean I should never build more than one concurrent index at a time within the entire cluster? If so, that is not clear from the documentation.
So if I start 2 index builds within a second of each other, you are saying that the first will not finish even though it started first because there is a long transaction from the second index build? I would expect much more deadlocks in this case, but building 5 indexes concurrently at a time of a total 15 indexes I only get one or two deadlocks. I would expect many more if I can’t even have 2 at once...
Thanks,
Jeremy
The only thing I can think of is that there's a foreign key from foo to
bar(id), but the create index on bar shouldn't prevent a share lock on
foo, even if such a restriction exists.
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
No, there are no foreign keys at all in this db. It is a logical replica.
Thanks,
Jeremy
On Tue, Dec 26, 2017 at 10:03 PM, Jeremy Finzel <finzelj@gmail.com> wrote:
Many thanks for the great and simple explanation.I was able to get this compiled, and ran the test before on stock 9.6.6, then on this patched version. I indeed reproduced it on 9.6.6, but on the patched version, it indeed fixes my issue!
Hi Jeremy,
I don't know if you were following the discussion over on the hackers list, but Alvaro has committed the fix and it should be in the 9.6.7 version when that gets released.
Cheers,
Jeff