Thread: Waiting for Commit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello, I don't know if this is the right place for this question,
sorry in advance.

So, the question is: how can I wait for a transaction commited data
to be available to other sessions programmatically? I need to read
data in a different session than the one that commits it, but I'm
having timing problems, and can't seem to be able to synchronize
them.

What I've got is something like this:

Session1> INSERT INTO Table VALUES(0);
Session1> COMMIT;
Session2> SELECT FROM Table;

And Session2 is not gettint the values I've just commited.

Many thanks,
Pablo

- --
An assertion is Zen only when it is itself an act and does not refer
to
anything that is asserted in it.
 -- (from Bruce Lee's book, The Tao of Jeet Kune Do)


-----BEGIN PGP SIGNATURE-----
Version: PGP 8.0.3

iQA/AwUBQGSRxr6KEogX0AkqEQKGLwCgmR+sn1/YnoRNlNIzK6OVkNo2cm8An2tm
b0dz1bNuAUpFtQbaa/KZHkl3
=ZkFI
-----END PGP SIGNATURE-----

"Pablo Montilla" <melkor@adinet.com.uy> writes:
> What I've got is something like this:

> Session1> INSERT INTO Table VALUES(0);
> Session1> COMMIT;
> Session2> SELECT FROM Table;

> And Session2 is not gettint the values I've just commited.

I don't believe that can happen, unless you've made a programming
error.

(1) Are you waiting for the COMMIT response to come back before you
issue the other SELECT?

(2) Are you running the other transaction in serializable mode?
If so, it won't see commits that happened later than its own BEGIN.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks for your response, I've been doing some tests, and got to a
point where I can put a case for consideration.

This is going get a bit more specific, so if this gets off topic,
please bear with me.

I'm programming in C# using the Npgsql driver (don't think its the
offending code, as i've traced all the code, and the commands are
going sequentially to the database).

This is a test case that fails:

01.database.connect();
02.database.execute("DELETE FROM test");
03.for(int i = 0; i < 30; i++) {
04.    database.beginTransaction();
05.    database.execute("INSERT INTO test VALUES(1, 'test')");
06.    database.commitTransaction();
07.}
08.IDataReader
09.reader = database.executeReader("SELECT * FROM test");
10.for(int i = 0; i < 30; i++) {
11.    assert(reader.Read());
12.}
13.database.disconnect();

The last part of the test, fails in the iteration 26. If I move the
transactions from lines 04 and 06 outside the loop, I don't see the
error.

Each reader, is created using another connection (the connection is
created automatically).

Any ideas? What I'm doing wrong?

Thanks,
Pablo
- --
I have never been able to understand why it is that just because
I am unintelligible nobody can understand me.
 -- Milton Mayer


-----BEGIN PGP SIGNATURE-----
Version: PGP 8.0.3

iQA/AwUBQGYfK76KEogX0AkqEQID+wCfWY0vb6yR69tFXnysfNv0gbkynhAAn3Jj
NIl1qXlXTDsz1NFlhrQ+4ri6
=6FYu
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, don't you love when in fact it is YOUR code that's at fault?
Let me assure you that I really tried to blame myself first (did it
for arround 12hours to no avail), but when all else fails...;o)

What I was REALLY doing:

Session1>SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN;
Session1>INSERT INTO test VALUES(1, 'test');
Session1>COMMIT;
Session1>ROLLBACK;
Session2>SELECT * FROM test;

It seems odd to me that the ROLLBACK was affecting the execution
(couldn't replicate the behavior in psql, maybe its a speed
thing...).

Anyway, many thanks for your time,
Pablo

PS: For anyone who cares, the ROLLBACK was generated by disposing the
NpgsqlTransaction, no matter it was committed. I will post it as a
bug, as Disposing should not be generating that nasty ROLLBACK...;o)
- --
Artificial intelligence is no match for natural stupidity.

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.0.3

iQA/AwUBQGY1Cr6KEogX0AkqEQJi6QCfd9pKE4ShhHyE8bWPcpR0qSuNf1EAoP0h
EF3Ya0/yGI5Bpj9T110coMu+
=+AVZ
-----END PGP SIGNATURE-----