Hi guys, I have a couple of questions about a recent concurrency test I did on SA. Someone in our team ran into a very intermittent issue where a certain operation he tried doing failed by ConcurrentUpdateError. I wrote a simple thread that can do query/update/insert/commit/rollback operations on demand and created two of those to create specific race conditions manually.. I found out some interesting stuff:
If I use sqlite, two threads cannot operate on the same ORM object. E.g.: t1.update(obj) t2.update(obj) t1.commit() t2.commit() t2.update(obj) would fail with an error saying "Database is locked". If I use mysql to do the same thing it goes through with no problems. The only time I could reproduce the concurrent update error is like this: t1.update(obj) t2.delete(obj) t2.commit() t1.commit() The last line will throw the error saying num updated rows is 0 where it was expecting 1. My first question is: Can this concurrent update error arise in any other situation? I also tried the following: t1.delete(obj) t2.delete(obj) t2.commit() t1.commit() This also fails with the same error saying the num deleted rows is 0 when it was expecting 1. My second question is: Should t1's transaction go through? I would think so. I understand why an update to a deleted row fails the entire transaction but I do not understand the motivation for failing an entire transaction because a row I wanted to delete is already deleted. This behavior is, as far as I can see, embedded in SA logic. So no matter which database I use, I will see this right? (unless something happens more fundamentally at the db level, like in sqlite, getting the database locked error) The final question is a design question. In a typical web application that can have 100's of simultaneous users submitting a handful of tasks, how do we avoid this kind of concurrency issues? Each request would be handled with a separate thread meaning if any of these threads try modifying the same db object, we have a potential problem. Do we need exclusive locks for this kind of situation? Does SA allow us to do that? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~----------~----~----~----~------~----~------~--~---
