SQLObject

On Thu, Mar 15, 2007 at 05:13:30PM +0100, sophana wrote:
> At the end, I have an sql error saying that there is no more connection
> possible. There are indeed 100 processes sleeping.
> What is creating all these connections?

   Seems .doInTransaction() must call conn.commit(True). Can you test if
this works and helps?

> Now some more questions. What is the difference between
> - using a transaction object
> - simply using connection.query('begin;') and connection.query('commit;')

   Transaction object properly reinitializes the underlying connection
(turning autocommit mode off and on), it has a different iterator for
InheritableSQLObject, it synchronizes cashes upon .commit().

> What about the cache? Do I have to add in sqlmeta: cacheValues=False?

   I think you don't.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

I had problems with sqlobject's doInTransaction() in the past. Ian wrote 
the following for me which I have been using successfully.

def doInTransaction(func, *args, **kw):
     """
     This routine can be used to run a function in a transaction,
     rolling the transaction back if any exception is raised from
     that function, and committing otherwise.

     Use like::

         doInTransaction(process_request, os.environ)

     This will run ``process_request(os.environ)``.  The return
     value will be preserved.
     """
     old_conn = sqlhub.getConnection()
     conn = old_conn.transaction()
     sqlhub.threadConnection = conn
     try:
         try:
             value = func(*args, **kw)
         except:
             conn.rollback()
             raise
         else:
             conn.commit()
             return value
     finally:
         sqlhub.threadConnection = old_conn
### /end doInTransaction ###

Hope this helps.

--
Rick

On Thu, 15 Mar 2007, sophana wrote:

> Hi
>
> I'm testing transactions with sqlobject.
> I found the doInTransaction method
>
> so I made a test:
> (python2.4 sqlobject 0.8.0 mysql innodb table)
>
> class TestSql(SQLObject):
>    k    = IntCol(default=None)
>    v    = IntCol(default=None)
>
>
> def init():
>    TestSql.dropTable()
>    TestSql.createTable(ifNotExists=True)
>
>    TestSql(k=1,v=1)
>    TestSql(k=2,v=2)
>
> def inc(k):
>    a=TestSql.select(TestSql.q.k==k,forUpdate=True)[0]
>    sleep(0.1)
>    a.v += 1
>
> def go2(k):
>    for a in range(100):
>        sqlhub.doInTransaction(inc,k)
>
> go2(1)
>
> At the end, I have an sql error saying that there is no more connection
> possible. There are indeed 100 processes sleeping.
> What is creating all these connections?
>
> Now some more questions. What is the difference between
> - using a transaction object
> - simply using connection.query('begin;') and connection.query('commit;')
>
> example:
>
> def inc(k):
>    connection.query('begin;')
>    a=TestSql.select(TestSql.q.k==k,forUpdate=True)[0]
>    sleep(0.1)
>    a.v += 1
>    connection.query('commit;')
>
> this works just fine. Any problem with this approach? Isn't it optimal
> and thread safe?
>
> What about the cache? Do I have to add in sqlmeta: cacheValues=False?
> When doing this, I saw the debug log and it is terribly not efficient.
>
> I tested this with 2 process and it was ok. Is it ok with 1
> multithreaded process?
> My application has 2 multithreaded (web server) process over 2 distant
> replicated mysql databases.
> I've read somewhere that mysql transaction should work with replicated
> databases.
>
> Thanks for your help
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

On Thu, Mar 15, 2007 at 11:29:31AM -0500, Rick Flosi wrote:
> def doInTransaction(func, *args, **kw):

   This code has been added to the SQLObject and is now in SQLObject 0.8.

>          else:
>              conn.commit()
>              return value

   I think here is a problem. conn.commit(True) should help - it releases
the underlying connection.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

Oleg Broytmann a écrit :
> On Thu, Mar 15, 2007 at 11:29:31AM -0500, Rick Flosi wrote:
>   
>> def doInTransaction(func, *args, **kw):
>>     
>
>    This code has been added to the SQLObject and is now in SQLObject 0.8.
>
>   
>>          else:
>>              conn.commit()
>>              return value
>>     
>
>    I think here is a problem. conn.commit(True) should help - it releases
> the underlying connection.
>
> Oleg.
>   
Yes it seems to work much better with commit(True).
Could you tell me a little more about how this works? what mean released
here?
Is it efficient? I mean, does it open a connection each time I make a
transaction?
How can I check that there is no more connection leak. (I checked the
mysql process list, and it seems ok...)
What about rollbacks? shoudn't they release the connection too? Or is it
always released?

About caches, how is it possible that I don't have atomicity problems,
if  the row instance is the same object between threads, how can
transactions work correctly if I don't get the same read value because
of a commit not done in another thread?
Should I use connection.queryAll instead?

Isn't it a limit of the object relational mapper?

Thanks very much for your great support!

Hello!

On Fri, Mar 16, 2007 at 12:33:49AM +0100, sophana wrote:
> Yes it seems to work much better with commit(True).

   Good. I will fix that. Thank you for testing!

> Could you tell me a little more about how this works? what mean released
> here?

conn = conn.transaction()

   creates a new transaction object; the object acquires a new low-level
connection from the pool and stores it; the low-level connection is removed
from the pool; "releasing" means "return it to the pool". For
single-threaded programs there is one connection in the pool.

> Is it efficient? I mean, does it open a connection each time I make a
> transaction?

   If the pool is empty a new low-level connection opened; if you have
disabled pooling (by setting conn._pool = None) the connection will be
closed instead of returning to the pool.

> What about rollbacks? shoudn't they release the connection too? Or is it
> always released?

   Yes. You can call as much .commit()'s, but after .rollback() you have to
call .begin() to acquire a new low-level connection. The last .commit()
should be called as .commit(True). Now you know why.

> About caches, how is it possible that I don't have atomicity problems,
> if  the row instance is the same object between threads, how can
> transactions work correctly if I don't get the same read value because
> of a commit not done in another thread?

   .commit() expires the object from all caches, so on the next access the
thread will redraw the object from DB. This doesn't work between processes,
of course.

   (Seems like a good piece of documentation. Need to insert this into
SQLObject.html...)

> Isn't it a limit of the object relational mapper?

   What limit?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

Oleg Broytmann a écrit :
> conn = conn.transaction()
>
>    creates a new transaction object; the object acquires a new low-level
> connection from the pool and stores it; the low-level connection is removed
> from the pool; "releasing" means "return it to the pool". For
> single-threaded programs there is one connection in the pool.
>   
>> Is it efficient? I mean, does it open a connection each time I make a
>> transaction?
>>     
>
>    If the pool is empty a new low-level connection opened; if you have
> disabled pooling (by setting conn._pool = None) the connection will be
> closed instead of returning to the pool.
>
>   
Why does it have to get a new connection? Why not using the current
thread connection, as there can't be any other request made?
>> What about rollbacks? shoudn't they release the connection too? Or is it
>> always released?
>>     
>
>    Yes. You can call as much .commit()'s, but after .rollback() you have to
> call .begin() to acquire a new low-level connection. The last .commit()
> should be called as .commit(True). Now you know why.
>
>   
Ok, but you use the doInTransaction method and an exception occurs, the
method calls rollback(). Shouldn'it release the connection to the pool also?
How can I detect that an exception occurred in the method? Even if I
don't detect that, I think the connection should be released in all cases.
>> About caches, how is it possible that I don't have atomicity problems,
>> if  the row instance is the same object between threads, how can
>> transactions work correctly if I don't get the same read value because
>> of a commit not done in another thread?
>>     
>
>    .commit() expires the object from all caches, so on the next access the
> thread will redraw the object from DB. This doesn't work between processes,
> of course.
>
>   
If thread A changes in a transaction an instance value (without a
forUpdate=True), but doesn't commit yet, and thread B access the same
row instance (without a forUpdate=True). Isn't thread B supposed to read
the old value of the instance? If thread B reads the same cached
instance than A, it will update the sqlobject instance inside thread A
with the old value, won't it?
My concern is that the transaction handling system in sqlobject works if
you use a sql level read locking (forUpdate=True). But if you don't do
that, the cache system can make things work not as you can expect. Am I
right? (note that I'm a real newbie in sql transactions)
This may be why you need to use the sqlmeta cacheValues=False in this case?
>    (Seems like a good piece of documentation. Need to insert this into
> SQLObject.html...)
>
>   
I found almost nothing about sqlobject transactions indeed.
Thanks

On Fri, Mar 16, 2007 at 01:56:49PM +0100, sophana wrote:
> Why does it have to get a new connection? Why not using the current
> thread connection, as there can't be any other request made?

   hub.getConnection() should do this. Don't know why it leaks connections.

> Ok, but you use the doInTransaction method and an exception occurs, the
> method calls rollback(). Shouldn'it release the connection to the pool also?

   .rollback() does this.

> If thread A changes in a transaction an instance value (without a
> forUpdate=True), but doesn't commit yet, and thread B access the same
> row instance (without a forUpdate=True). Isn't thread B supposed to read
> the old value of the instance?

   It is. But now when your program has grabbed an instance it is the
responsibility of the program to redraw it from the DB when another thread
updated the DB. The instance in your program is beyond SQLObject reach.

> This may be why you need to use the sqlmeta cacheValues=False in this case?

   This is a reason. cacheValues=False  makes things simpler but with the
price of speed. You can have your speed with cacheValues=True, but you have
to synchronize objects in your threads yourself. SQLObject cannot do both at
once. Either speed or convenience.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.