Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
1.4k views
in Q2A Core by

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

Q2A version: 1.7

1 Answer

+2 votes
by
selected by
 
Best answer

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:

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.
by
Thanks, Pupi! :)
...