Re: [sqlalchemy] how to handle deadlock
try: Perform table transaction break except: rollback delay try again to perform table transaction I'm sure you will get the gist of the above and why. I also set my isolation_level to READ UNCOMMITED. Thanks, Garyc --- On Thu, 5/6/10, rajasekhar911 rajasekhar...@gmail.com wrote: From: rajasekhar911 rajasekhar...@gmail.com Subject: [sqlalchemy] how to handle deadlock To: sqlalchemy sqlalchemy@googlegroups.com Date: Thursday, May 6, 2010, 5:23 AM Hi I am using sqlalchemy0.5.5 in my TG2 app with mysql(innodb) database My applicaton is multithreaded. there are multiple tasks that run at certain intervals. in one of my tables i am getting the following deadlock error. Deadlock found when trying to get lock; try restarting transaction the query was failing while trying to delete in the following way. DBSession.query(Metrics).filter(Metrics.type_id==type_id).filter(Metrics.cdate = date1).delete() so i changed the code to rows = DBSession.query(Metrics).filter(Metrics.type_id==type_id).filter(Metrics.cdate = date1).all() for row in rows : DBSession.delete(row) assuming that it will scan less no:of rows while deleting. In this way i am no longer seeing the deadlock error. but if anybody can clarify that this is reliable enough or suggest any other options , that would be really helpful thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] setting the transaction isolation_level?
Hello, I'm using SQLAlchemy with sqlite 3.6.23. I would like to know how you set the transaction isolation_level=immediate. I think this will help with the deadlocks I was having. Just dont know how to implement this. Thanks, Garyc -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] setting the transaction isolation_level?
Thanks Michael.
This is like trudging through knee high mud to find a needle.
oh boy. On I go.
Thanks,
Garyc
--- On Tue, 4/27/10, Michael Bayer mike...@zzzcomputing.com wrote:
From: Michael Bayer mike...@zzzcomputing.com
Subject: Re: [sqlalchemy] setting the transaction isolation_level?
To: sqlalchemy@googlegroups.com
Date: Tuesday, April 27, 2010, 3:30 PM
gary clark wrote:
Hello,
I'm using SQLAlchemy with sqlite 3.6.23. I would like
to know how you set
the transaction isolation_level=immediate.
I think this will help with the deadlocks I was
having. Just dont know how
to implement this.
I've added ticket 1784 to document this.
We support directly PRAGMA read_uncommitted=%d, via the
possible values
READ UNCOMMITTED or SERIALIZABLE:
create_engine('sqlite://', isolation_level='READ
UNCOMMITTED')
I don't understand what the Pysqlite docs are referring to
when they come
up with three values, “DEFERRED”, “IMMEDIATE” or
“EXCLUSIVE” - SQLite's
official documentation only describes two values for
PRAGMA
read_uncommitted. However to set pysqlite
specific values, use a
PoolListener.
http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener
Thanks,
Garyc
--
You received this message because you are subscribed
to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the
Google Groups sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Database locking
Thanks Michael. I am going to do some more research on SQLAlchemy and how it handles locks and its interaction with sqlite. I will try and post my findings next week. Thanks, Garyc --- On Mon, 4/26/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Re: Database locking To: sqlalchemy@googlegroups.com Date: Monday, April 26, 2010, 4:39 PM gazza wrote: Thanks Michael. Not sure if this is the only thing I need to do. Why disable connection pooling? it means the app will release filehandles completely when a connection is released, and also that individual threads in your app will use distinct connections. The QueuePool implementation, not the default for SQLite, will also provide the latter, but I've observed that QueuePool doesn't really provide much if any performance gain over straight file open/close per connection. SQLite really doesn't like multithreaded access on a single connection. Thanks, Garyc On Apr 26, 10:32 am, Michael Bayer mike...@zzzcomputing.com wrote: gazza wrote: Hello, I am using multiple servers to access a sqlite db via sqlachemy. I am using the latest SQLAlchemy and pysqlite-2.6.0. I get permenant locking and it causes mayhem. I did rebuild sqlite to make it --enable-threadsafe. Whats the approach to handle this problem? one helpful thing is to disable connection pooling. seehttp://www.sqlalchemy.org/docs/reference/dialects/sqlite.html#threadi... . Thanks, garyc -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
