0

I'm using Npgsql 3.2 and it seems that the driver is now preparing statements automatically.

I see the following in the PostgreSQL logs:

LOG: execute <unnamed>:SELECT * FROM database.update_item($1, $2, $3 , $4)

I may be wrong but this is not activated on the connection string and I see no previous 'prepare' call in the logs.

What am I missing ?

Also I'm using Dapper 1.50.2 on top of Npgsql to query the database. This is not yet implemented at this level but I see that there are some talks about such a feature on GitHub.

I'm using a READ COMMITTED transaction to update a row in the database. The row is updated twice with 2 disctinct statements.

When playing the statements one by one in a pgadmin query window it works fine.

When the statements are played by the driver through execute, the first statement seems to put locks on the record, and the second statement hang.

Here is the queries ran in the query window (runs to completion) :

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM database.update_item(params...);

SELECT * from database.update_item(params...);

ROLLBACK;

The corresponding PG logs:

LOG: statement: BEGIN;
LOG: statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
LOG: statement: SELECT * FROM database.update_item(params...);
LOG: statement: SELECT * from database.update_item(params...);
LOG: statement: ROLLBACK;

The PostgreSQL logs when played by Npgsql:

LOG:  statement: BEGIN
LOG: statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG:  execute <unnamed>: select * from database.update_item(params...)
LOG:  execute <unnamed>: select * from database.update_item(params...) <-- hangs

Any help appreciated.

EDIT 1:

Additional information: The update_item() plpgsql function is updating the row with an EXECUTE statement.

EDIT 2:

Added PG logs for query window.

1 Answers1

0

First, Npgsql's automatic preparation feature is opt-in - it's not activated without the connection string. Even then, the same SQL needs to be executed several times (5 by default) before Npgsql prepares it. See the documentation for more details.

Regarding your deadlock, are you running your code concurrently? In other words, do you have multiple transactions at the same time, updating the same rows? If so, then this is probably the expected PostgreSQL behavior and has nothing to do with Npgsql. When you update rows in a transaction, these rows are locked until the transaction is committed. The fix in general is to update rows in the same order. See the PostgreSQL documentation for more details.

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • What about the 'execute ' through Npgsql ? No, no concurrent transaction. –  Mar 14 '17 at 11:55
  • Unless I'm mistaken, that simply means that the statement hasn't been prepared yet by Npgsql. It's a bit difficult to understand the relationship between the deadlock and the preparation in your question (and there shouldn't be on) - can you please confirm that the deadlock occurs regardless of preparation? Does the deadlock always occur, or is it occasional? Finally, a complete code sample (including the function) would help to reproduce the issue. – Shay Rojansky Mar 14 '17 at 15:53
  • The deadlock does not occurs from a pgadmin query windows, where the queries are run one by one. There are logged in pglog as statement. It occurs every time when the queries are run from Npgsql on the same connection. There are logged as execute . Those are just facts. I agree with you, preparation should probably not mess with locks. I'm looking for clues. –  Mar 14 '17 at 16:40
  • When running them from pgadmin, do you also have a transaction in progress? Also, can you turn on statement logging in your postgresql.conf and post the results? – Shay Rojansky Mar 14 '17 at 16:41
  • Sorry, didn't see you already posted that - it seems you do have a transaction even with pgadmin. Can you also post the PG logs for that run? – Shay Rojansky Mar 14 '17 at 16:42
  • I'm gonna open a Npgsql issue about that. I think that there is an issue on both sides. On my side, I would say that the transaction scope is not disposed if Npgsql encounter an exception. This misled me thinking that this was a lock problem. Sorry a bout that. See you on GitHub. –  Mar 16 '17 at 10:47