How to lock database row on select?
Basil Shubin
I have use SQLAlchemy 0.3 with MySQLdb 1.2.1 How I can lock row in
database table when I select it? So nobody can access it in same time.
BTW I use tables with InnoDB engine.
--
Basil Shubin
Freelance Software Developer
Simon Wittber
you can do this reliably.
SA does provide a mechanism which provides optimistic concurrency,
which is often overlooked. Optimistic concurrency mechanisms solve the
same problem that application level row locking attempts to solve.
From
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_arguments:
version_id_col=None - an integer-holding Column object that will be
assigned an incrementing counter, which is added to the WHERE clause
used by UPDATE and DELETE statements. The matching row count returned
by the database is compared to the expected row count, and an exception
is raised if they dont match. This is a basic "optimistic concurrency"
check. Without the version id column, SQLAlchemy still compares the
updated rowcount.
Basil Shubin
> Hi friends!
>
> I have use SQLAlchemy 0.3 with MySQLdb 1.2.1 How I can lock row in
> database table when I select it? So nobody can access it in same time.
> BTW I use tables with InnoDB engine.
Okey, I discover 'lockmode' parameter. And how I can use it (or anything
else) to get this: if I have select the row, so NOBODY can access to it?
Moreover there be should an exception raised, so I can catch it and show
to user message box about that database entry is in use by somebody? Is
this possible and how do you solve this task? I need it to disallow
mutiple editing/deleting of same entry by different users in same time.
Thanks!
Mike Bernson
row_obj = query_obj.select(where, lockmode='update').execute()
Basil Shubin
> use the lockmode='update' on a query object.
>
> row_obj = query_obj.select(where, lockmode='update').execute()
>
self.localTowns = Table('local_towns', self.metadata,
Column('id', Integer, primary_key=True),
Column('title', Unicode(50)),
mysql_engine='InnoDB')
self.localTowns.create(checkfirst=True)
item = self.localTowns.select(self.localTowns.c.id==itemId,
lockmode='update').execute()
and I got this error :-(
File "/usr/lib/python2.4/site-packages/sqlalchemy/sql.py", line 1332,
in select
return select([self], whereclause, **params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/sql.py", line 65,
in select
return Select(columns, whereclause = whereclause, from_obj =
from_obj, **kwargs)
TypeError: __init__() got an unexpected keyword argument 'lockmode'
Basil Shubin
> use the lockmode='update' on a query object.
>
> row_obj = query_obj.select(where, lockmode='update').execute()
self.localTowns = Table('local_towns', self.metadata,
Column('id', Integer, primary_key=True),
Column('title', Unicode(50)),
mysql_engine='InnoDB')
self.localTowns.create(checkfirst=True)
self.session = create_session()
mapper(dbsql.LocalTown, self.localTowns)
item =
self.session.query(dbsql.LocalTown).with_lockmode('update').execute().selectone(self.localTowns.c.id==itemId)
I got this:
File "/home/bashu/work/devel/wxExpress/lib/database.py", line 95, in
GetLocalTown
item =
self.session.query(dbsql.LocalTown).with_lockmode('update').execute().selectone(self.localTowns.c.id==itemId)
TypeError: execute() takes at least 2 arguments (1 given)
Basil Shubin
What is problem? Let see, I have developing one GUI application that's
no more that database frontend. Users has ability to add/edit/delete
data into database through GUI forms. As I understand if two users start
working with the same data at same time it can lead to big trouble, e.g.
user One has open entry to edit it, but user Two has delete it same
time. So my task is to make some kind of restrictions for a things like
above I describe.
Excuse me, but I luck knowledge about databases/SQLAlchemy and don't
know how I can solve above problem.
Thanks in advance!
Michael Bayer
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.
Basil Shubin
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?
Michael Bayer
noticed all it does is hang up your application. if youre looking to
communicate information between clients youll have to build something
on your own to do that.
Daniel Miller
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
Basil Shubin
>
> 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)
> );
Is it possible to create table like above via SQLAlchemy? For
construction like this:
self.locks = Table('locks', self.metadata,
Column('table_name', Unicode(15)),
Column('locked_row_id', Integer),
Column('table_name', 'locked_row_id',
primary_key=True))
I got ab error:
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
284, in create
self.metadata.create_all(connectable=connectable,
checkfirst=checkfirst, tables=[self])
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
862, in create_all
connectable.create(self, checkfirst=checkfirst, tables=tables)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 413, in create
self._run_visitor(self.dialect.schemagenerator, entity,
connection=connection, **kwargs)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 437, in _run_visitor
element.accept_schema_visitor(visitorcallable(self, conn.proxy,
connection=conn, **kwargs), traverse=False)
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
882, in accept_schema_visitor
visitor.visit_metadata(self)
File "/usr/lib/python2.4/site-packages/sqlalchemy/ansisql.py", line
637, in visit_metadata
table.accept_schema_visitor(self, traverse=False)
File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line
268, in accept_schema_visitor
return visitor.visit_table(self)
File "/usr/lib/python2.4/site-packages/sqlalchemy/ansisql.py", line
658, in visit_table
self.append("\t" + self.get_column_specification(column,
first_pk=column.primary_key and not first_pk))
File
"/usr/lib/python2.4/site-packages/sqlalchemy/databases/mysql.py", line
435, in get_column_specification
t = column.type.engine_impl(self.engine)
AttributeError: 'str' object has no attribute 'engine_impl'
OnInit returned false, exiting...
Basil Shubin
>> table_name varchar,
>> locked_row_id int,
>> PRIMARY KEY (table_name, locked_row_id)
>> );
self.locks = Table('locks', self.metadata,
Column('table_name', Unicode(15), primary_key=True),
Column('locked_row_id', Integer, primary_key=True))
Thats it! Thanks!