[Gnumed-devel] re commit

[syan tan]

I'm not sure if Karsten is agreeing with me, or just being condescending. 

Here's a demonstration that a cursor from one connection doesn't 
conflict with a cursor from a different connection.
In java, and in hibernate ( which is middleware to 10 different 
relational database engines) , the semantics of
one transaction / one session per connection holds. 

I can see the point one may want a shared connection amongst many 
processes guaranteed to be read only, although I'm not sure about the 
use of _ pseudo users to achieve it.  As to why you may want many 
cursors if you can't individual commit or rollback independent 
transactions on them,
they could be useful when you have a readonly connection, and you want 
separate threads simultaneously updating different parts,
of a user interface: (but in hibernate, a session is a thread local 
object, so that wouldn't work in hibernate).

Othewise, I just thought cursors  was a cute way of focusing on what 
one was doing at the time.
I discovered the one transaction per connection concept a while ago, 
when I and a couple of student buddies were doing
a project on remote invocation of 3 tiers of servers, and a hacky idea 
was needed when one had  Account manager remote
objects, and a transaction had to be made between 2 accounts, but one 
needed a SQL transaction to span across 2 invocations of the remote
object. Prior to this, invocations on the remote objects were stateless, 
i.e. the remote object didn't maintain any state between operations.
So we had to come up with SessionAccountManagers , which were created by 
a remote factory,  but that's another newbies' hacking story...

What it shows below, is that cursor one deletes all the records in its 
own transaction, but cu2 doesn't see that. After the deletion, cursor 
one can see
that the table is empty. But then it rollbacks the transaction. It then 
sees the original table contents. cursor2 on the other hand, was not 
affected by
cursor one's transaction at all. If cursor one had committed, a select 
by cursor2 would see the transaction.

>>> c = PgSQL.connect("localhost::gnumed")
>>> c2 = PgSQL.connect("localhost::gnumed")
>>> cu = c.cursor()
>>> cu2 = c2.cursor()
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>> cu2.execute("select * from enum_comm_types")
>>> print cu2.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>> cu.execute("delete from enum_comm_types")
>>> cu2.execute("select * from enum_comm_types")
>>> print cu2.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[]
>>> c.rollback()
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>> cu2.execute("select * from enum_comm_types")
>>> print cu2.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>>



Here's when I try to delete id=1 in cursor 2 , and then update in cursor 
1 where id=1  , and then as expected, the shell blocks,
because cursor 2 hasn't been committed it's write, and cursor 1 won't 
proceed until cursor 2 has committed or aborted (which it can't
because the console is blocked in cursor 1 operation -->
deadlock  :   cursor2 waits for console to issue commit/abort , console 
waits for cursor 1 return , cursor 1  waits for cursor2 commit/abort.

I'm sounding like Karsten with 101 transactions   ; )


>>> cu2.execute("delete from enum_comm_types where id= 2")
>>> print cu.fetchall()
[]
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>> c2.commit()
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 'TEST'], [7, 
'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 'TEST7'], 
[15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], [23, 
'test21'], [24, 'test20']]
>>> cu2.execute("delete from enum_comm_types where id= 1")
>>> cu.execute("update enum_comm_types  set description='hel' where id=1")