INSERT concurrency causes deadlock about cstore_fdw HOT 11 CLOSED

Hello Chris
thanks for the detailed report. We will look into this. Could you help about reproduction steps ? Following information will be most useful

1. number of paralel insert sessions you were running.
2. some sample data.

We will try to reproduce the issue and post our findings and resolutions here.

As a side note
cstore_fdw insert feature is meant to be used for bulk row inserts. Using it for single row inserts might cause some performance issues for read queries. If possible I suggest you to use an temporary regular table for single row inserts and bulk insert them into cstore table periodically.

from cstore_fdw.

@netshade thanks for reporting this. I'll look into this soon.

from cstore_fdw.

@mtuncer Sure thing:

1. 4 threads were running, using a connection pool between them.

WITH temp_table(id,project_id,time,value,count) AS (
  VALUES (1,1,1432214440,10.0,1), 
         (2,1,1432214440, 24.323, 1), 
         ..REPEAT RANDOMLY GENERATED VALUES 1024 TIMES...
 ) INSERT INTO cstore_fdw_table SELECT * FROM temp_table

And yeah, that's exactly why I was doing bulk INSERT loads. While the batch sizes were pretty small, I wanted to test the effect of multiple concurrent loads in a cstore_fdw table; I'm not interested in single row inserts at all.

@pykello Awesome, thanks.

from cstore_fdw.

run the same query with 4 threads (no connection pooling) few times no problem is encountered.

from cstore_fdw.

I wrote a java client that has connection pooling (c3p0) with 4 threads. It issues the same command continuously. I let it run for a while but can not hit this issue.

I am stopping investigation

@netshade If you still having this problem please let us know preferably with reproduction steps. So that we can diagnose it better.

from cstore_fdw.

I'm taking a brief look at this. Haven't found the main issue yet, but I've found a related issue.

When I put a breakpoint right after we grab the ExclusiveLock in CStoreBeginForeignModify and look at pg_locks at that point, I see two locks acquired on the cstore table: (1) ExclusiveLock, (2) RowExclusiveLock.

The 2nd lock is acquired automatically by PostgreSQL and I think it's the lock to grab for updates/inserts/deletes.

We added the ExclusiveLock so we don't have concurrency issues with COPY. I think what we should have done instead was to change the lock type in COPY to RowExclusiveLock. This is the lock grabbed at PostgreSQL's copy.c too. We didn't need to add the ExclusiveLock to CStoreBeginForeignModify.

I'll look more to find the issue @netshade reported, but I guess maybe following PostgreSQL's locking convention for inserts/updates/deletes may solve this problem and prevent other unexpected behaviors.

from cstore_fdw.

Steps for reproducing this issue:

• Create the benchmark table:

CREATE FOREIGN TABLE benchmark (
    id int,
    project_id int,
    time int,
    value int,
    count int
)
SERVER cstore_server
OPTIONS (compression 'pglz');

• Create insert.sql as:

WITH temp_table (id, project_id, time, value, count) AS(VALUES(1,2,3,4,5)) INSERT INTO benchmark SELECT * FROM temp_table;

• Run pgbench:

/opt/postgresql/9.4/bin/pgbench -c 16 -j 4 -f insert.sql -n -T 300 postgres

Then there's lots of deadlock reports:

Client 8 aborted in state 0: ERROR:  deadlock detected
DETAIL:  Process 11899 waits for ExclusiveLock on relation 16392 of database 13088; blocked by process 11909.
Process 11909 waits for ExclusiveLock on relation 16392 of database 13088; blocked by process 11899.
HINT:  See server log for query details.

I'm looking into this. Will keep this thread updated.

from cstore_fdw.

I think what happens is:

1. Thread A acquires RowExclusive lock (postgres does this, not cstore).
2. Thread B acquires RowExclusive lock (since RowExclusive lock doesn't conflict with RowExclusive locks. See this ).
3. Thread A tries to get Exclusive lock. Blocks, since thread B is holding RowExclusive lock which conflicts with Exclusive lock.
4. Thread B tries to get Exclusive lock. Blocks, since thread A is holding RowExclusive.
5. None of them can move further and deadlock happens.

I'll try to find a way to resolve this.

from cstore_fdw.

So, we need a lock type which:

• is self-conflicting, because we don't support concurrent updates yet.
• doesn't conflict with ROW EXCLUSIVE, so the deadlock doesn't happen.

I think what we want is a SHARE UPDATE EXCLUSIVE lock. I'll test this and create a pull request.

from cstore_fdw.

maybe we should also look at copy operation since it also uses exlusive lock

from cstore_fdw.

@mtuncer : Your previous comment looks like the issue we ran into with a customer. If there's no issue, can we open one specifically for COPY?

from cstore_fdw.