Thread: singletons per row in table AND locking response

singletons per row in table AND locking response

From
Dennis Gearon
Date:
When locking is involved, does a transaction wait for access to a row or table, or does it just fail back to the
callingcode? Would it be up to my PHP code to keep hammeing for access to a row/table, or could a user defined function
dothat? 

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
persite 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
totables 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
treeshave rights." 

# The right to harmony and balance between everyone and everything: "We are all interdependent."


See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'

Re: singletons per row in table AND locking response

From
Dennis Gearon
Date:
I could have just as easily described it as a table of SERIALS, one per row, instead of per column.

:0)

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
treeshave rights." 

# The right to harmony and balance between everyone and everything: "We are all interdependent."


See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'

Re: singletons per row in table AND locking response

From
"Hartman, Matthew"
Date:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: singletons per row in table AND locking response

From
Sam Mason
Date:
On Tue, Jul 07, 2009 at 06:45:36PM -0700, Dennis Gearon wrote:
> 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?

It defaults to waiting for the lock to be released, but will fail if you
tell it to.  Sounds as though you want to be using row-level locks,
it will allow more concurrency where possible, but table level locks
are also possible.  PG also makes a distinction between "share" locks
(multiple transactions can have a share lock on any table or row) and
"update" locks (this locks out share and other update locks).

--
  Sam  http://samason.me.uk/