PGDAC and Pessimistic Edit with AutoCommit

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 6
Joined: Fri 29 Apr 2011 11:16

PGDAC and Pessimistic Edit with AutoCommit

Post by durumdara » Wed 16 Mar 2016 12:39

Dear Support!

I tried to simulate EDB / SDAC pessimistic lock with autocommit mode.

The experience was:
1. I started two instance with one PGQuery(lmPessimistic).
2. I edit a row in App1.
3. I edit same row in App2.
4. I got lock obtaining error.

It was great. Then I tried two Queries (Q1, Q2).

1. I started two instance with 2 PGQueries (lmPessimistic) with locktest1, and locktest2 table.
2. I edit App1.Query1 row.
3. I edit App2.Query1 row - I got lock obtaining error.
4. I edit App1.Query2 row (then I have two App1 locked queries!)
5. I edit App2.Query2 row - I got lock obtaining error.
For this moment everything is fine.
6. I post App1.Query2 row.
7. Then App2.Query2 row editing also causes lock obtaining error(!)
8. I post App1.Query1 row.
9. Then App2.Query2.Edit works.

So the problem that if I have two Queries as opened in A1, the A2 can't lock Q2 before all of them canceled or posted!

Is this based on PGSQL lock + AutoCommit, or is it a bug?

Thanks: dd

Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: PGDAC and Pessimistic Edit with AutoCommit

Post by azyk » Thu 17 Mar 2016 09:34

PostgreSQL has no support for multiple transactions for the same connection instance. The described behavior may occur if Query1 and Query2 use the same instance of TPgConnection.

As you use pessimistic mode, then on calling (2) the App1.Query1.Edit, dataset starts a transaction (since it is not yet started) and locks the locktest1 record. After calling (4) App1.Query2.Edit, the dataset locks the locktest2 record in the context of this transaction, since both datasets use the same instance of TPgConnection.

After calling (6) App1.Query2.Post, the dataset writes the applied changes to the same transaction. The transaction remains open at this, since it contains uncommitted changes. Both locks applied on calling (2) App1.Query1.Edit and (4) App1.Query2.Edit still remain. Therefore, on calling (7) App2.Query2.Edit, you get lock obtaining error.

After calling (8) App1.Query1.Post, the transaction contains no more uncommitted changes and closes. Locks applied by calls to (2) App1.Query1.Edit and (4) App1.Query2.Edit are removed. Therefore, on calling (9) App2.Query2.Edit, the error message doesn't appear.

The behavior of PgDAC described above is correct.

Post Reply