Basil Shubin wrote: > Michael Bayer пишет: >> this example is confusing the Table object with a mapped class. >> >> selecting off a table with the FOR UPDATE clause added looks like: >> >> result = table.select(localTowns.c.id==itemid, for_update=True) >> >> "lockmode" is an ORM-level concept when youre dealing with mapped >> classes. > > Thanks, now I realise how to use it. But here is another problem, how I > can check if the appropriate row was selected for update? It's needed > because I want show error message box in a case where row is in update > status. As I see if I try access already selected row it just hang up > until the row was released, alas, no exception was raised. So my > question is how I can prevent user from selecting the row that was > selected by other, how I can do this by programming? >
I have to agree with Michael here, you probably shouldn't be using pessimistic locking. I'm a PostgreSQL user, so I'll tell you how I would do it with that. You'll have to translate this to use it with MySQL (I think that's what you said you're using). Also, this approach will only work if you control all access to the table (i.e. only if no external applications will be updating the table--otherwise you'll need to use pessimistic locking). Also, this solution will only work for tables with with an integer primary key, although it could be adapted to work with any table by creating additional lock tables for each primary key type. Create a table in your database to hold locks. It should be something like this: CREATE TABLE locks ( table_name varchar, locked_row_id int, PRIMARY KEY (table_name, locked_row_id) ); Each time you start to edit a row in a given table, insert a record into the locks table with the table name and the primary key of the row you're going to update. If the insert fails due to a "unique constraint" violation, then the record is already locked by someone else. When you've finished updating the record (assuming the lock insert succeeded), delete the corresponding row from the locks table. Note that locked rows may be locked indefinitely if the code that locked them crashes before it is able to delete the lock record. You may need to cleanup the locks table periodically. My colleague told me this is re-inventing the wheel. I guess it is if there's a lock mode that lets you SELECT ... FOR UPDDATE NO WAIT (i.e. raise an exception if someone else has selected the same row for update), but I don't know of any such feature. ~ Daniel --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
