Posted by Dikkie Dik on 05/20/06 01:16

>> If your database brand (I am not familiar with postgress) does not
>> support transactions or locking, you can invent your own. With a
>
> Web-based transactions that require user interaction in the middle
> of them are dangerous and pretty much useless. Part of the problem
> is that there is no timeout value which is not too short, too long,
> or both at the same time. In a tech support or customer service
> environment, where techs edit customer records, consider the
> possibilities for getting fired or murdered by co-workers if your
> computer crashes in the middle of an edit and delays the daily
> billing run.

If only one "finance" user can edit the record, there's no problem. Tech
support is a separate problem altogether, as they can access the
database without your application. So whatever, you come up with, an
administrator can always circumvene it.

Apart from that, I think that getting a message "this message is locked
by ..." is by far more acceptable than randomly incorrect bills. That is
why I started my previous mail with: The first question is: Can two
users access the same record at all? For many systems, this is not the case.
A good second question is: what is the impact of concurrency errors? For
a billing system, I'd take my time to investigate all consequences. For
a hitcounter, I don't mind missing a count once in a while.

>> timestamp field and a user (or sessionId) field, you can see what user
>> has requested a lock. If it is locked too long ago, it is free. If it is
>> locked by another user or session, it is locked. As SQL commands are
>> usually atomic (I have yet to encounter a database brand that has unsafe
>> commands), an update command can be forged that takes the full locking
>> condition in its WHERE clause.
>
>> Look up the section about transactions in the database's documentation.
>> The fear of "hanging" open transactions is often solved by the fact that
>> connections are reset by PHP after executing the script, and the
>
> In other words, in a PHP-based setup, the transaction is reset
> before the user has a chance to answer the "are you sure?" prompt.
> (Note to self: never permit creation of a user by the name of
> "sure"). Net effect: they're useless since the transaction is
> always rolled back.

No. Like a previous poster wrote, you can check the state of the record
before updating. You do _that_ in the same transaction as the update. If
you detect a collision, it is up to you what you do with it: not
updating and getting back to the user is just one option. Only if the
script crashes during the collision check, the transaction would be
rolled back automatically instead of keeping a lock that is never needed
anymore.
I would never keep a lock between two calls to the webserver, as nobody
can guarantee that the second call will ever be made.

Best regards

[Back to original message]