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
