Posted by Dikkie Dik on 05/19/06 23:34

The first question is: Can people access the same record in parallel?

When defining tables, I always recognize 3 categories:
- Definition tables (better known as lookup tables)
Contain only read only data that rarely changes.
No concurrency problems.
- Live data tables can be a problem
- Log tables are add-only. I never put transactions on them,
because I'd rather have an illogical order of the records
than missing records. Log tables should never be locked.

When records are only available for a single user, there's only the
possibility of the same user (or someone abusing an account) messing
with his own data (two different browsers, maybe?).
If your database brand (I am not familiar with postgress) does not
support transactions or locking, you can invent your own. With a
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
database rolls back any open transactions in a connection when it is
reset/closed. Again, I do not know how Postgress handles transactions
and connections.

Best regards

rich wrote:
> I am building an app using php and postgresql. My questionis this.
> How do you handle people wanting to make parallel changes to a record.
> Since in web apps you are doing a select, bring over a record then
> updating it, there is no lock on that record while you are making the
> changes in your browser window. Does transactions handle that? Do you
> make your selection and update all part of the same transaction? This
> whole scenario has me stumped.
>

[Back to original message]