RE: [PEAR-DEV] Idea for pear sequence tables

Since the table is so small, the whole thing would probably be locked when you lock a single row anyway. The smallest element that can be locked by the db is equal to the db block size. Which is normally set to match the OS blocksize so that a db write = an OS write. The end result is that there most likely would be no difference, and locking the table is faster than locking individual rows. If the database is designed well, all this should happen in memory, so the time that is takes to pass the query to the db should exceed the time that the table is locked... > -----Original Message----- > From: Stefan Neufeind [mailto:stefan@neufeind.net] > Sent: Wednesday, September 24, 2003 3:37 AM > To: pear-dev@lists.php.net > Cc: demian@phpkitchen.com > Subject: Re: [PEAR-DEV] Idea for pear sequence tables > > > On 24 Sep 2003 at 9:13, Demian Turner wrote: > > > Apologies for the slow reply to this thread, just finished a very busy > > contract. > > Regarding putting all sequences in a single table, I'm glad this is > > being > > discussed and that the concept has been accepted in principal, when > > you have 30+ tables in a DB the additional sequences are a real mess > > :-) > > > > Browsing through Drupal code a few weeks ago I came up with a simple > > solution that may help progress towards a patch. They use a > > procedural function for the mysql driver that: > > > > - takes care of table locking > > - using REPLACE creates a new sequence when one does not exist > > But as far as I remember several people have pointed out that locking > the whole table (which stores multiple sequences) is not an ideal > solution as it affects speed of all processes which need to access > the sequence-table. On a high-load-DBMS this might lead to > performance-impacts where the one-sequence-one-table-solution would > work better. > > Hmm - we could solve this maybe with serverside functions ... > unfortunately mysql 3.x (still widely used) doesn't support this :-)) > > So what way should be chosen? Could anyone maybe give an educated > guess on the speedloss by locking the whole table? > > > Stefan > > -- > PEAR Development Mailing List (http://pear.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >