Mon, If I understood well, skip-locking isn't for you. IMHO, if you have a unique process (mysqld) that manages your tables and statements are causing deadlock, maybe you should try InnoDB. Try simulate a parallel environment changing the storage engine and tell us. (Take a look in the modifier 'LOCK IN SHARE MODE' of InnoDB too.)
Somebody in the list can give him a better suggestion? Regards, Nilton Moura. 2010/9/17 monloi perez <mlp_fol...@yahoo.com>: > Thanks Nilton and sorry for the rest for not giving details on the issue. > > I think the issue happened when an insert was followed by a select on the > same table or perhaps vice versa. What's obvious is that the queries started > to pile up and their status shows "system lock". We research on skip locking > and tried however we still had the same issue again. So our bet was really > on the insert and select that caused the table to be locked. > > Are there mysql configurations that you can suggest other than skip-locking > to at least fix the issue? > > Thanks, > Mon > > > ________________________________ > From: Nilton Moura <my...@nmoura.eti.br> > To: mysql@lists.mysql.com > Sent: Thu, September 16, 2010 11:07:24 PM > Subject: Re: skip locking > > Hi, > > The first main difference is that InnoDB has transactional > capabilities (all-or-nothing) and MyISAM not yet. If you need to use > COMMIT on some statements, you need to use InnoDB. The second point is > the locking of MyISAM, which is on table-level. If your environment is > mixed with read/write concurrency, this is not good, because > table-level locking is exactly what you read. InnoDB has unlimited > row-level locking. > > If you want to use MyISAM, read about the locking. By default, write > requests take priority over read requests. But you can change the > priority of your requests. For example, you can use the modifier > LOW_PRIORITY with statements that update update tables like INSERT or > UPDATE. But take care, because if read statements keep arriving, it is > possible for a low-priority write request never to be performed. > > The last tip: even MyISAM with table-level locking, it support > concurrent inserts. If you have many UPDATE statements and/or DELETE, > you have holes in your tables, and this disable concurrent inserts. Or > you optimize table (to defragment) or change the concurrent_insert > system variable to 2, to force concurrent inserts, or both. But, read > about this too. > > There are many differences about this two storage engines, and I hope > I have helped you. > > Nilton Moura. > > 2010/9/16 monloi perez <mlp_fol...@yahoo.com>: >> Any idea on this? >> >> -Mon >> >> >> >> >> ________________________________ >> From: Michael Satterwhite <mich...@weblore.com> >> To: mysql@lists.mysql.com >> Sent: Mon, September 13, 2010 1:07:38 AM >> Subject: Re: skip locking >> >> Received. >> >> >> >> On Sunday, September 12, 2010 09:32:12 am monloi perez wrote: >>> Hi All, >>> >>> Sorry if I posted on the wrong list. >>> >>> I've had this issue with my previous server already, seems like >>> skip-locking configuration does not seem to work on myisam tables. >>> >>> Also what is the difference between myisam and innodb tables. The reason >>> I >>> wanted to know is that I think skip-locking works better in InnoDB. Or >>> perhaps the right question is when do I choose myISAM and when do I >>> choose >>> InnoDB. My current server is running on an 8-core cpu with 32G of memory >>> and only running CMS or news based web applications. What do you suggest >>> that we use for the storage engine? >>> >>> Thanks, >>> Mon >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mlp_fol...@yahoo.com >> >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mlp_fol...@yahoo.com > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
