Re: singletons per row in table AND locking response

> Dennis Gearon
> Sent: Tuesday, July 07, 2009 9:46 PM
>
> When locking is involved, does a transaction wait for access to a row
or
> table, or does it just fail back to the calling code? Would it be up
to my
> PHP code to keep hammeing for access to a row/table, or could a user
> defined function do that?

I do not know the answer to your question off hand, but be wary of
pausing or hammering the database to establish a lock. Consider the
implications of what happens when the application or thread with the
lock crashes.

Say for example that User A establishes a lock on a table and crashes.
The lock persists. User B tries for a lock, is denied, and enters a loop
of constantly trying. He'll be stuck in limbo until the first lock is
cleared.

I think the model of denying the lock and perhaps retrying with a set
limit on attempts would be a better approach.

For what it's worth, in my own PHP/PostgreSQL application I handle
locking through the application and database. The database has a "locks"
table. The application requests a lock by looking for a non-deleted lock
in that table for whatever object (table or a row within a table) it
wants. If no such lock exists, one is created and returned to the
application. The lock is released at the end of the current task. The
advantage is that if anything crashes, there is a page in the
application that an administrator can delete any lock from, or see who
holds a lock on what from when.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital

>
> I'd like to have a certain object in my PHP application have
essentially
> individual SERIAL rows per object created site wide. So only one
script
> instance at a time in apache can have access to a row to read and
> increment a value in a row.
>
> Example, (totally random idea, example only), any user on site can
create
> a group. Each group assigns group_user_ids per site member in his
group,
> starting at zero for each new user joining a group, no matter their
> site_id.
>
> My choices so far seem to be:
> IN PHP, Use a system file for locking only one instance of the
class
> gets access to the table.
> IN PHP, Use the transaction failure to hammer the database for
one
> instance of the class.
> IN PHP, Use the transaction failure to hammer the database for
each
> ROW's instance of a class.
> IN POSTGRESQL, use the transaction failure to hammer the database
for
> each ROW's instance of a class.
>
> But maybe there's more to the locking than failed transactions for
UPDATE,
> some kind of sequential queueing of access to tables or rows for
> transactions?
>
> I'm trying to minimize the interfaces, cpu time, etc involved in
getting
> access to the table.
>
>
> extremely basic SQL for this idea.
>
> CREATE TABLE group (
> group_id SERIAL NOT NULL,
> CONSTRAINT PK_group PRIMARY KEY (group_id)
> );
>
> CREATE TABLE singletons_for_last_grp_mbr_id_issued (
> group_id INTEGER NOT NULL,
> last_grp_mbr_id_issued INTEGER DEFAULT 0 NOT NULL,
> CONSTRAINT PK_singletons PRIMARY KEY (counts_per_main, main_id)
> );
>
> CREATE UNIQUE INDEX IDX_One_Group_Row_Only ON
> singletons_for_last_grp_mbr_id_issued (group_id);
>
> ALTER TABLE singletons_for_last_grp_mbr_id_issued
> ADD CONSTRAINT group_singletons_for_last_grp_mbr_id_issued
> FOREIGN KEY (group_id) REFERENCES group (group_id)
>
> Dennis Gearon
>
> Signature Warning
> ----------------
> EARTH has a Right To Life
>
> I agree with Bolivian President Evo Morales
>
> # The right to life: "The right for no ecosystem to be eliminated by
the
> irresponsible acts of human beings."
>
> # The right of biosystems to regenerate themselves: "Development
cannot be
> infinite. There's a limit on everything."
>
> # The right to a clean life: "The right for Mother Earth to live
without
> contamination, pollution. Fish and animals and trees have rights."
>
> # The right to harmony and balance between everyone and everything:
"We
> are all interdependent."
>
>
> See the movie - 'Inconvenient Truth'
> See the movie - 'Syriana'
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general