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.
But...
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
PGDAC and Pessimistic Edit with AutoCommit
Re: PGDAC and Pessimistic Edit with AutoCommit
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.
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.