Inferring whether or not we are dealing with a serialization failure from the exception type

[Former user]

Here is the problem: In practice it is nearly impossible to avoid transaction deadlocks (it is certanly impossible in sqlite). So the best thing the programmer can do is to expect some transactions to be rolled back because of a deadlock occurring, being ready to retry them. Generally PostgreSQL's documentation recommends this approach for applications. The problem I see here is that there is no database-independent method for the programmer to correctly identify the type of problem in order to safely retry its transaction. If think some/most/all database adapters raise OperationalError, which in DB-API 2.0 is described as:

OperationalError Exception raised for errors that are related to the database's operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.

This exception obviously signals a wide variety of database errors and in general the transaction may not be safely retried after such an error have occurred. But for example, if we are dealing with PostgreSQL and the specific exception error code happens to be 40001 (SERIALIZATION FAILURE), then the programmer may consider retrying the transaction.

So I propose a new type of exception to be added to sqlalchemy's exception hierarchy which signals a serialization failure and guarantees that a potential transaction retrial is both safe and reasonable. Each specific database subsystem should be able to recognize a serialization failure, so that the programmer need not to deal with all database's quirks.

[Mike Bayer]

Providing access to DBAPI return codes is mostly a dupe of #295. I've linked this issue there so that we can reference the additional ideas you've put forth even though #295 should stay the home base for this feature request.

While I agree with the general request for error code reporting stated in #295, it would be a bad idea for us to begin editorializing the standard DBAPI exception hierarchy. We pass the original exception classes (wrapped, but still in the same correspondence) so that users aren't presented with a new abstraction system to further confuse an error condition. Its also not a given that when DBAPIs report a "serialization failure" that they're all going to use OperationalError (I know the spec states as such, but in reality every DBAPI has a different notion of what exceptions to use when).

As far as detecting the condition here, the job would be to compile all error codes across all databases and DBAPIs which we support, which indicate transactions that can be "safely retried" due to deadlock. Any serious effort would contain working implementations of #295 to the degree that it's possible, as well as this flag for at least SQLite, Postgresql, Oracle, MySQL, Firebird, and MS-SQL, for at least the DBAPIs sqlite3, psycopg2, pg8000, pg-zxjdbc, cx_oracle, oracle-zxjdbc, MySQLdb, Mysql-zxjdbc, Mysql-pyodbc, Kinterbasdb, MSSQL-pyodbc. All DBAPIs report errors differently.

I'm also not sure that databases like MySQL, Firebird or Oracle provide such an error condition in most cases - MySQL MyISAM has no deadlocks, InnoDB I've never seen them, and Oracle I'm pretty sure just locks up tight and you're out of the game, though my knowledge could be out of date on this. This is why I must insist on a comprehensive approach which has taken into consideration the real-world behavior of all our major backends - a generic API feature cannot be added without evidence that the approach is feasable for most backends, and can be backed up by unit tests. Otherwise its just another flag that you can only use when you're on one specific backend, in which case its no better than just plain #295 by itself.

Here's a script which I wrote for Pycon2009 that illustrates a deadlock on PG (which has by far the most amazing deadlock detection ive ever seen): http://bitbucket.org/zzzeek/pycon2009/src/tip/chap5/deadlock.py . An approach like this can perhaps form the basis of a unit test for the feature.

[Former user]

I see your point. I can imagine some databases not having the ability to detect serialization features. But I do not think that this is a reason not to provide the feature (or flag if you like) that I propose. Here are my reasons:

1) Sqlite and PostgreSQL certanly have this ability. These databases definitely have a say in the free software community.

2) If a database can not detect serialization failure, then it simply does not support the programming technique that I described. This is OK in the same way that some databases chooses not to support stored procedures or even transactions!

3) The programming technique that I described is really powerful. I remember how many hours I have spent with MS SQL Server checking if all the locks are obtained in the proper order to avoid possible deadlocks. I do not want to do this again :( Nevertheless the support for the technique I described is very raw -it is definitively not a first class citizen in any ORM.

[Mike Bayer]

Just for the record you can implement this yourself for PG and SQLite, in the way you like, using a ConnectionProxy:

http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.html#sqlalchemy.interfaces.ConnectionProxy

just catch exceptions inside of cursor_execute and rethrow in the way you like.

For features like these that by definition can't really be implemented very completely and have a lot of moving parts that are only nailed down by lots of experimentation, they are better as usage recipes or examples, at least to start. That way people who use them take the responsibility of understanding how they work.

[Former user]

Well, this is a classical vicious circle: A feature does not get implemented because nobody uses it, and nobody uses it because it is not implemented. In the meantime most programmers just can not figure out how to do the job right. A sad story of engineering short-sightedness in my opinion.

[Former user]

Thinking a bit more about the solution you proposed (I am an sqlalchemy newbie): It looks very elegant and easy to do. Thanks.

[Mike Bayer]

closing this for now. I'd prefer if some future DBAPI could help us out with stuff like this, as I really prefer for exceptions to be passed through by default (else it becomes "sqlalchemy magic" and our problem).