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
LOCK TABLES issue
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
Fulltext search with multiple document revisions
MySQL full text indexing looks attractive, but I'm puzzled as to how to use it for my application. I'm storing documents that I'd like to be full-text indexed, but each document can have multiple revisions. I'm currently storing each revision in full. The fulltext relevance criteria don't work well with this: if a document has many revisions, then all the words in that document become (artificially) less relevant. Perhaps the boolean search facilities will overcome this difficulty in 4.0.1, I'm unclear on that. Can anyone suggest ways around this? 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
Re: Fulltext search with multiple document revisions
Thanks for the note, Sergei. I admit to being confused by the documentation on boolean search. In particular, exactly what the problem domain for the two algorithms is supposed to be (and how relevancy is computed with boolean search). I don't understand why boolean search has (it seems) a totally different approach to relevancy. Or to ask it a different way, which applications would use non-boolean search, and why? Also my understanding from the documentation and release notes is that boolean search is only available in 4.0.1, which is not yet available. Is this correct? Thanks, Bob Hi! On Dec 15, Bob Sidebotham wrote: MySQL full text indexing looks attractive, but I'm puzzled as to how to use it for my application. I'm storing documents that I'd like to be full-text indexed, but each document can have multiple revisions. I'm currently storing each revision in full. The fulltext relevance criteria don't work well with this: if a document has many revisions, then all the words in that document become (artificially) less relevant. Perhaps the boolean search facilities will overcome this difficulty in 4.0.1, I'm unclear on that. Can anyone suggest ways around this? Either recompile mysqld with modified myisam/ftdefs.h (using simplified weighting scheme), or - better - use boolean search, because in your application you perform boolean queries anyway. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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
