Error for user choosing the best answer: MySQL query error 1206: The total number of locks exceeds the lock table size

Question

PHP Question2Answer MySQL query error 1206: The total number of locks exceeds the lock table size - Query: INSERT INTO qa_userpoints (userid, aselecteds, points) VALUES ('986', @_aselecteds:=(SELECT COUNT(*) AS aselecteds FROM qa_posts AS userid_src JOIN qa_posts AS questions ON questions.selchildid=userid_

src.postid WHERE userid_src.userid='986' AND userid_src.type='A' AND NOT (questions.userid<=>userid_src.userid)), 300+(50*@_aselecteds)) ON DUPLICATE KEY UPDATE aselecteds=@_aselecteds, points=300+(5*qposts)+(10*aposts)+(0*cposts)+(5*aselects)+(50*@_aselecteds)+(0*qupvotes)+(-2*qdownvotes)+(0*aupvotes)+(-2*adownvotes)+(1*qvoteds)+(1*avoteds)+(0*upvoteds)+(0*downvoteds)+bonus, referer: http://ww.spaceamigos.com/1949515/voce-ja-aprendeu-a-esperar

Answer 1

Based on the MySQL docs, you need to assign more memory to lock managing:

Error: 1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL)

Message: The total number of locks exceeds the lock table size

InnoDB reports this error when the total number of locks exceeds the amount of memory devoted to managing locks. To avoid this error, increase the value of innodb_buffer_pool_size. Within an individual application, a workaround may be to break a large operation into smaller pieces. For example, if the error occurs for a large INSERT, perform several smaller INSERT operations.

Here are a couple of links that explain how to do so:

• https://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os
• https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

If you don't have access to perform these changes (e.g. you're using shared hosting) you'll have to ask your hosting provider to change it.

Comments

Thanks, Pupi! :)