> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
