Re: LOCK TABLES issue
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)
RE: LOCK TABLES issue
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 u
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
