Thanks, Rama, I'm using MyISAM, in part because I want to use full text indexing, when binary search mode stabilizes in 4.0.1. I don't believe that full text indexing is supported by innodb. I'm also a little reluctant to go to innodb because of the added complexity of the system.
Bob "Ramaraju.R.V" wrote: > > You are using InnoDB or MyISAM??? > > If u r going for InnoDB, > Refer to InnoDB Engine manual for the version 3.23.47 at www.innodb.com. > Here you details of different LOCK types. > You can select the appropriate lock type at row level for different > possibilities. > > Hope this works!! > > Cheers :) > Rama Raju > > -----Original Message----- > From: Bob Sidebotham [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 07, 2002 2:00 PM > To: [EMAIL PROTECTED] > Subject: Re: LOCK TABLES issue > > I'll try posting this again, because nobody has replied to it. > > To put the problem more succinctly: I think there is a flaw in the mysql > LOCK statement semantics, because if I use LOCK then all tables that are > accessed while the LOCK is active must be locked for READ or WRITE > access (or insert, but that doesn't help me). This is done I think to > protect the application writer against accidently not getting locks that > are required. What is needed is an explicit way to allow a particular > table to participate while the LOCK statement is active, but without > locking it. I've suggested that LOCK ... table NOLOCK would be an > appropriate addition to the LOCK statement for these semantics--it > allows a table to be explicitly left unlocked. More details are > available in my original post, which is included here. > > I would appreciate comments on this. > > (If there's a better place to post this, can someone let me know that, > too?). > > Thanks, > Bob > > Bob Sidebotham wrote: > > > > I have an application with fairly typical locking requirements: 99% of > > the requests are read-only (with an exception that I will specify). > > Update requests are relatively rare. There's a half-dozen tables, and > > since the inter-relationships are a little complex. I found it easiest, > > given the performance constraints of my application, to simply lock ALL > > the tables for READ for the read requests, and to lock ALL of them for > > WRITE in the case of an update. I think this is a fine, time-tested, > > conservative locking strategy. It basically can't fail, and gives me > > adequate performance for my needs. > > > > I have ONE table which records access counts/times for each user for > > individual objects in the system. This table needs to be updated on > > every access. This table can be updated correctly without obtaining any > > locks. It is subject to being read at unpredictable times during any of > > the read-only requests. > > > > Since the access table can be read during any of the read-only requests, > > and since it can be read at any time during the transaction, I have to > > obtain at least a READ lock for this table along with the other locks > > (even though I don't really need a read-lock) because MySQL insists that > > if any tables are locked, then every table you wish to access must also > > be locked (I assume this feature is intended as a reasonable precaution > > against accidently forgetting to lock a table that must participate in a > > "transaction"). Unfortunately, to update this table I have to either > > upgrade to a WRITE lock or drop the lock altogether. It's obvious that > > upgrading to a WRITE lock will cause all my read-only operations to > > pileup on the WRITE lock. It's also possible for me to drop all the > > locks (and record the accesses at the very end of the transaction). Less > > obvious, but I think true, is that this *also* causes serialization, > > because MySQL must implicitly require all the READ locks on the table to > > be dropped before allowing me to update it (is this true? If it isn't > > true, it should be!). > > > > I cannot, by the way, use READ LOCAL because I want to use both UPDATE > > and REPLACE on the table. > > > > So I seem to be caught between a LOCK and a hard place, so to speak. > > > > What I would like to see would be something like: > > > > LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; > > > > The semantics of this would be to explicitly recognize that t3 does not > > need to be locked and can therefore be read or written after this LOCK > > TABLES request (as opposed to any table that is not mentioned which > > cannot be read or written). NOLOCK would, of course, be incompatible > > with READ or WRITE locks, but would be compatible with other NOLOCK > > locks or with no lock at all, for both read and write operations. > > > > If anyone can suggest another way to do this, I'd appreciate it. > > Otherwise, is there any reaction to this proposal? Does anyone think > > this is useful functionality? > > > > Thanks, > > Bob Sidebotham > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
