Thread: Transaction handling in extended query mode and Sync message issues

Hello:

I continue to work in the implementation of the 3.0 protocol in C#, i'm 
making a test that consist on:

- Create a database.
- Create a table in the new database.
- Start transaction.
- Insert 100 rows of data in the new table.
- Commit transaction.

For start a transaction i send a Query message with this as statement text:

START TRANSACTION ISOLATION LEVEL READ COMMITTED

For commit a transaction i send a Query message with this as statement text:

COMMIT TRANSACTION

For execute the inserts i use parametrized commands with Extended query 
mode, sending this sequence of messages:

- Parse & Flush ( Only for the first command )
- Describe & Flush ( Only for the first command )
- Close Portal ( begining at second command )
- Bind & Flush
- Execute & Sync

But the Sync message seems to be commiting the transaction, if i send a 
Flush message all works as expected with inserts, but create database & 
table will not work.

In the extended query mode documentation i see this:

"Note:  Sync does not cause a transaction block opened with BEGIN  to be 
closed. It is possible to detect this situation since the ReadyForQuery 
message includes transaction status information."

I have tested the same cycle with a BEGIN WORK but having the same 
problem, any idea on what i'm doing wrong ???



Tanks in advance :)






-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Carlos Guzman Alvarez wrote:
> Hello:
> 

Hello Carlos.

> I continue to work in the implementation of the 3.0 protocol in C#, i'm 
> making a test that consist on:
> 
> - Create a database.
> - Create a table in the new database.
> - Start transaction.
> - Insert 100 rows of data in the new table.
> - Commit transaction.
> 
> For start a transaction i send a Query message with this as statement text:
> 
> START TRANSACTION ISOLATION LEVEL READ COMMITTED
> 
> For commit a transaction i send a Query message with this as statement 
> text:
> 
> COMMIT TRANSACTION
> 
> For execute the inserts i use parametrized commands with Extended query 
> mode, sending this sequence of messages:
> 
> - Parse & Flush ( Only for the first command )
> - Describe & Flush ( Only for the first command )
> - Close Portal ( begining at second command )
> - Bind & Flush
> - Execute & Sync
> 
> But the Sync message seems to be commiting the transaction, if i send a 
> Flush message all works as expected with inserts, but create database & 
> table will not work.
> 

Sorry for late response...
I could finally get Npgsql to talk protocol 3.0 version :) It is not 
100% but it is near...
I give it a try in a test similar to yours... I didn't send the create 
database commands just the row insertion.

in both sequences, I could get the desired behaviour.
I could send the begin transaction in simple query mode, send the insert 
in extended mode and send a commit or rollback in simple mode sending 
the sync message in the end of extended mode.


Are you still having problems with it?

I hope it helps.


-- 
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

Hello:

> Sorry for late response...
> I could finally get Npgsql to talk protocol 3.0 version :) It is not 
> 100% but it is near...

Great !! I'm going to open my code at sourceforge :)

> I give it a try in a test similar to yours... I didn't send the create 
> database commands just the row insertion.
> 
> in both sequences, I could get the desired behaviour.
> I could send the begin transaction in simple query mode, send the insert 
> in extended mode and send a commit or rollback in simple mode sending 
> the sync message in the end of extended mode.
> 
> 
> Are you still having problems with it?


I have it fixed sending the Sync message only for ALTER, CREATE AND DROP 
commands and seems to work as expected, iwill try to do more tests with 
transactions and the sync message soon,





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

- Miembro del Proyecto FirebirdSQL.
- Miembro honorario de la Fundación FirebirdSQL.

http://www.firebirdsql.org

Carlos Guzman Alvarez <carlosga@telefonica.net> writes:
> In the extended query mode documentation i see this:
> "Note:  Sync does not cause a transaction block opened with BEGIN  to be 
> closed. It is possible to detect this situation since the ReadyForQuery 
> message includes transaction status information."
> I have tested the same cycle with a BEGIN WORK but having the same 
> problem, any idea on what i'm doing wrong ???

Did you figure this out?  I can't see any way that a Sync message would
close a transaction started with BEGIN (or START TRANSACTION).  It runs
the same code we run at the end of a simple-Query message, and we'd
definitely have noticed if that were causing premature commit ...
        regards, tom lane

Hello:

> Did you figure this out?  I can't see any way that a Sync message would
> close a transaction started with BEGIN (or START TRANSACTION).  It runs
> the same code we run at the end of a simple-Query message, and we'd
> definitely have noticed if that were causing premature commit ...

Thanks for answer, I'm going to make more tests but in first tests the 
sync message is committing the transaction :) but i don't know why.





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain