singletons per row in table AND locking response

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 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'

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

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/