sqlobject-discuss

[SQLObject] Problems with transactions

[Cyril E. <cy...@de...>]

Hello, again.

Put it simply Transactions do not work. In fact you must have 
autoCommit=True to make them work. Here is a transcript of what happens 
when you set autoCommit=false:

  1/Pool    :  ACQUIRE pool=[]
  1/QueryIns:  INSERT INTO Repository.Category (id, description, 
language, number) VALUES ('FR_FR.CAT1', 'Consultation', 'fr_FR', 'A')
  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
  1/ROLLBACK:  auto
  1/Pool    :  ACQUIRE pool=[]
  1/QueryOne:  SELECT description, language, number FROM 
Repository.Category WHERE id = 'FR_FR.CAT1'
  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
  1/ROLLBACK:  auto


The QueryOne request fails always because the INSERT request have been 
roll back. The reason is that SQLObject creates, and closes a connection 
for each SQL statement. Why this behaviour? Should it not be possible to 
open the database connection when creating the SQLObject connection and 
releasing it _only_ when its parent is destroyed by the client code?


Again, I'm ready to go into the code but I need to know the method to 
send patches.


Cyril Elkaim

[SQLObject] Re: Problems with transactions

[Cyril E. <cy...@de...>]

I answer my own message,

I didn't understand that I must pass a transction object in lieu of the 
connection object. That works this way. But I think there is a problem 
yet with the pool of sql connections when doing multithreading. Not sure 
that the same SQL connection is used twice by _runWithConnection during 
a transaction implying multiple SQL statements. Any ideas?

Cyril Elkaim

Cyril Elkaim wrote:
> Hello, again.
> 
> Put it simply Transactions do not work. In fact you must have 
> autoCommit=True to make them work. Here is a transcript of what happens 
> when you set autoCommit=false:
> 
>  1/Pool    :  ACQUIRE pool=[]
>  1/QueryIns:  INSERT INTO Repository.Category (id, description, 
> language, number) VALUES ('FR_FR.CAT1', 'Consultation', 'fr_FR', 'A')
>  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
>  1/ROLLBACK:  auto
>  1/Pool    :  ACQUIRE pool=[]
>  1/QueryOne:  SELECT description, language, number FROM 
> Repository.Category WHERE id = 'FR_FR.CAT1'
>  1/Pool    :  RELEASE (implicit, autocommit=0) pool=[]
>  1/ROLLBACK:  auto
> 
> 
> The QueryOne request fails always because the INSERT request have been 
> roll back. The reason is that SQLObject creates, and closes a connection 
> for each SQL statement. Why this behaviour? Should it not be possible to 
> open the database connection when creating the SQLObject connection and 
> releasing it _only_ when its parent is destroyed by the client code?
> 
> 
> Again, I'm ready to go into the code but I need to know the method to 
> send patches.
> 
> 
> Cyril Elkaim
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by BEA Weblogic Workshop
> FREE Java Enterprise J2EE developer tools!
> Get your free copy of BEA WebLogic Workshop 8.1 today.
> http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click

Re: [SQLObject] Re: Problems with transactions

[Ian B. <ia...@co...>]

Cyril Elkaim wrote:
> I answer my own message,
> 
> I didn't understand that I must pass a transction object in lieu of the 
> connection object. That works this way. But I think there is a problem 
> yet with the pool of sql connections when doing multithreading. Not sure 
> that the same SQL connection is used twice by _runWithConnection during 
> a transaction implying multiple SQL statements. Any ideas?

It should be.  If you turn on debugging, you should see connection IDs, 
which should help you see what queries belong to the same transaction. 
I'm not entirely comfortable with how that all works, so there certainly 
may be bugs, but I've tried to make it work.

The Transaction object fiddles with the underlying connection, calling 
the connection's methods with the transaction object as "self".  This 
way it overrides some specific methods (those that get connections) even 
if they are called indirectly.

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

[SQLObject] Re: Problems with transactions

[Cyril E. <cy...@de...>]

Ian,

the set() method doesn't take Transaction in account if an object has 
been retrieved outside a Transaction. ie:

This works:
BEGIN
get_object
modify
set()
COMMIT/ROLLBACK

But this doesn't:
get_object
modify
BEGIN
set()
COMMIT/ROLLBACK

The reason is the _connection property is setted during object creation 
only. A simple solution is to add connection to the argument list of set 
(set(self, connection=None,**kw).

For the moment when I use a transaction I explicitly do:
  obj._connection = trans_connection

This way it works

BTW I think that I have found where the problem with the validators 
lies, i'm testing.

Regards
Cyril Elkaim