Re: [Tutor] Record Locking and Access - newbye

[Magnus Lycka]

mjekl wrote: 
> Here's my problem.
> Using python dbapi I get groups of records back from a database into a list
> for example to feed a "List Form" which is just a grid on a form (the db is
> about 9000 thousand records - so I suppose there's not a big problem to
> fetch all records in a go!).

If you meant 9 000, it's no problem, if you meant 9 000 000, it might be 
a bit slow...
 
> Now when a user selects a record from a grid on screen I can send him to
> another form "Record Form" where he can edit the record. This way I can have
> only one query to the database and have the users working locally on there
> record sets. But when a user edits an existing record I puzzled to think
> that another user might be changing that record at the same time (because
> they are not locking any records and are using lists with the record
> contents locally)! This could cause for example that user A changes record 1
> from "bar" to "foo" and updates the db, just after user A user B updates the
> db with a change in the same record from "bar" to "foobar" (never seeing
> user As changes)!!!
> 
> I guess this is a very common problem with a standard solution. How can I
> avoid this?

Certainly. It's also a pure SQL/database problem which has little to
do with Python... As far as I know, there is nothing particular in the
DB API that will help you with this.

The solution depends on several things. Which RDBMS are you using?

One solution which works regardless of what database you use is to have a
timestamp column in your table, and only update the row if the timestamp 
is the same as when you selected the row. Always set timestamp to CURRENT
TIMESTAMP (or whatever it's called in your database) whenever you update the
table. Simply include the timestamp column in the WHERE clause of the UPDATE.
If someone else touched the row since you fetched it, your update won't
change any rows. In other words, cursor.rowcount will be 0, not 1 as expected.

For this timestamp solution to work, you need to be sure that the timestamp
isn't messed up. If you use adodbapi on Windows NT, Windows will truncate the
microseconds from your timestamp, which will cause this to fail unless you
do some special trick. One solution to that is to cast the timestamp column
to CHAR, e.g. "SELECT FOO, BAR, BAZ, CAST(TSTAMP AS CHAR(26)) FROM MYTABLE".

The other solutions involve the use of SQL commands such as SET TRANSACTION
ISOLATION LEVEL or LOCK TABLE, but you'd better check the docs for your own
database to sort that out.

-- 
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/  mailto:magnus at thinkware.se