Michael Bayer wrote:
Optimistic concurrency checking simply means when you UPDATE or DELETE rows, you insure that the returned row count matches correctly. This is how Hibernate does it, and its how SQLAlchemy does it.
I don't think you've got that quite right. Checking the affected row count alone does not guard against most concurrency violations. In fact, the only assurance you get from that is that rows being updated/deleted have not previously been deleted. It does not detect if the data that's being modified has been modified by another process or session. Hibernate implements optimistic concurrency with versioning. According to the documentation[1], there are three different strategies that Hibernate uses: 1. My least favorite approach requires an extra select before update/delete. The version number or original column values are checked for concurrency violations by Python code instead of in the WHERE clause of the SQL statement. This approach can be used with databases that don't support row count. However, for 100% reliability a pessimistic lock is required for a very short time from just before the concurrency check until after the update/delete is executed. 2. Each time a modification is committed the version of the row being committed is compared to the one in the database. If the version in the database is not the same as the version being committed then a concurrent modification has occurred and a concurrency exception is raised. If the versions are the same then everything is OK and the version is bumped during the update/delete to signify a modification has occurred. The update statement for this type of concurrency might look something like this: UPDATE table SET col_1='abc', col_2='xyz', ..., version=5 WHERE id=12354 AND version=4 or DELETE FROM table WHERE id=12345 AND version=4 Notice the version check in the WHERE clause. Each statement would do nothing if the version had changed. The ORM must check the row count after the UPDATE/DELETE to detect if a concurrency violation actually occurred. 3. Another method (I first ran into this one in the .Net DataAdaptor, but Hibernate does it too) is to compare the original column values of the columns being updated with values in the database. If they are different then a concurrency violation occurs. The update statement looks like this: UPDATE table SET col_1=<new value>, col_2=<new value>, ... WHERE id=12354 AND col_1=<original col 1 value> AND col_2=<original col 2 value> ... Again, the ORM must check the row count to detect if a concurrency violation has occurred. It seems like this type of concurrency check wouldn't be too difficult to implement in SQLAlchemy since AttributeManager tracks original column values. Am I totally wrong here? Is SQLAlchemy actually doing some type of optimistic concurrency checking that I don't know about? I couldn't find anything more than a row-count check in the source code, and I never noticed additional WHERE conditions or concurrency checks when debugging the SQL generated by SA. Please enlighten me if I'm wrong. ~ Daniel [1] http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#transactions-optimistic ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
