Transaction Isolation Level

[martinrame]

Hi, this morning I needed to do a couple of DDL commands in a PostgresSql 8.3 database, and the database took forever to execute the commands. The problem turned out to be that the table involved had many locks.

When my app connects to the database, it has an explicitly set TransactionIsolationLevel := tiReadCommited. If I use tiNone, the locks dissapear, allowing me to do the DDL commands.

...but, what happens with Transactions?. I debugged Zeos and found that when the connection is in tiNone, the Begin...Commit commands are ommited, them only work when TI is tiReadCommited.

Is there a way to use Transactions and avoid the locking problem?.

Thanks in advance,
Leonardo.

[Wild_Pointer]

Hello, matinrame

If you want all DDL commands in single transaction, then you should use them. I would suggest using tiNode and calling BEGIN, SET TRANSACTION ISOLATION LEVEL and COMMIT manualy. This way you'll be able to handle your transactions as you want.
The other thing is the lock issue. I think you should check the reason for it (pgAdmin has tool "server status" witch shows the locks) and eliminate in your DDL script.

[martinrame]

So according to your suggestion, every time I need to start a transaction I should do this?:

BEGIN
SET TRANSACTION ISOLATION LEVEL := tiReadCommitted;

then
COMMIT or ROLLBACK

and
SET TRANSACTION ISOLATION LEVEL := tiNone again?

[Wild_Pointer]

Hello, martinrame.

I guess you are mixing SQL and delphi code into one . What I wanted to say is you should set AutoCommit to false and TransactIsolationLevel to tiNone on your TZConnection component. That way Zeos doesn't manage transactions. You should execute 'START TRANSACTION ISOLATION LEVEL READ COMMITTED;' to start transaction and 'COMMIT;' or 'ROLLBACK;' to finish it.

I hope that was helpful.

[martinrame]

Thanks Wild_Pointer, yes, I written in a mixed Delphi/Sql!

The solution was:

At connection:
TransactionIsolationLevel := tiNone;

At StartTransaction:
TransactionIsolationLevel := tiReadCommited;

At Commit:
Commit;
TransactionIsolationLevel := tiNone;

At Rollback:
RollBack;
TransactionIsolationLevel := tiNone;

[Wild_Pointer]

Yes - that does the trick. Setting transaction isolation level to tiReadCommited starts the new transaction, then Commit commits it and starts another one, setting to tiNone - cancels the transaction. So in fact instead of 1 transaction you create 2 - the second of them ends as soon as it starts. Not very efficient. If you would issue Begin and commit directly, you could avoid such behavior.

This confuses me a lot. Why, oh why, the new transaction is started when the old one is commited or rollbacked??? Why?? Who needs this??? Is this only postgresql behavior or is it inherited behavior across all database systems?

As I see from the code - theres no way to start the transaction manually using TZConnection component - it just fires the OnStartTransaction event.

Code: Select all

procedure TZConnection.StartTransaction;
begin
  CheckAutoCommitMode;

  if FExplicitTransactionCounter = 0 then
    AutoCommit := False;
  DoStartTransaction;
  Inc(FExplicitTransactionCounter);
end;

I'd be really grateful if someone explained this to me: why a great tool like Zeos is "a bit confusing" when it comes to transaction control???