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?
2010/9/17 monloi perez <mlp_folder@stripped>:
> 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?
> From: Nilton Moura <mysql@stripped>
> To: mysql@stripped
> Sent: Thu, September 16, 2010 11:07:24 PM
> Subject: Re: skip locking
> 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_folder@stripped>:
>> Any idea on this?
>> From: Michael Satterwhite <michael@stripped>
>> To: mysql@stripped
>> Sent: Mon, September 13, 2010 1:07:38 AM
>> Subject: Re: skip locking
>> 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
>>> 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
>>> 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?
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1