| Bug #56405 | Deadlock in the MDL deadlock detector | ||
|---|---|---|---|
| Submitted: | 31 Aug 2010 13:26 | Modified: | 20 Nov 2010 19:23 |
| Reporter: | Jon Olav Hauglid | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S1 (Critical) |
| Version: | 5.5 | OS: | Linux (Ubuntu 64 bit) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[2 Sep 2010 16:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117440 3132 Dmitry Lenev 2010-09-02 Draft fix Bug #56405 "Deadlock in the MDL deadlock detector". Make LOCK_open an rwlock. The main goal of this commit is to study performance implications of such approach to fixing this bug.
[3 Sep 2010 11:07]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117508 3132 Dmitry Lenev 2010-09-03 Alternative draft fix Bug #56405 "Deadlock in the MDL deadlock detector". Changed rwlock which prefers reader to disallow situation in which lock can be owned by reader and there are readers waiting. The intent behing this commit is to study performance implications of such approach to fixing this bug.
[6 Sep 2010 17:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117634 3090 Dmitry Lenev 2010-09-06 A temporary workaround for bug #56405 "Deadlock in the MDL deadlock detector". Deadlock could have occurred when workload containing mix of DML, DDL and FLUSH TABLES statements affecting same set of tables was executed in heavily concurrent environment. This deadlock occurred when several connections tried to perform deadlock detection in metadata locking subsystem. The first connection started traversing wait-for graph, encountered sub-graph representing wait for flush, acquired LOCK_open and dived into sub-graph inspection. When it has encounterd sub-graph corresponding to wait for metadata lock and blocked while trying to acquire rd-lock on MDL_lock::m_rwlock (*) protecting this subgraph, since some other thread had wr-lock on it. When this wr-lock was released it could have happened (if there was other pending wr-lock against this rwlock) that rd-lock from the first connection was left unsatisfied but at the same time new rd-lock request from the second connection sneaked in and was satisfied (for this to be possible second rd- request should come exactly after wr-lock is released but before pending wr-lock manages to grab rwlock, which is possible both on Linux and in our own rwlock implementation). If this second connection continued traversing wait-for graph and encountered sub-graph representing wait for flush it tried to acquire LOCK_open and thus deadlock was created. This patch tries to workaround this problem but not allowing deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead deadlock is reported. Other possible solutions are either known to have negative effects on performance or require much more time for proper implementation and testing. No test case is provided as this bug is very hard to repeat in MTR environment but is repeatable with the help of RQG tests. @ sql/mdl.cc Moved Deadlock_detection_visitor::m_current_search_depth to parent class to make it available in TABLE_SHARE::visit_subgraph(). Added MDL_wait_for_graph_visitor::abort_traversal() method which allows to abort traversal of a wait-for graph and report a deadlock. @ sql/mdl.h Moved Deadlock_detection_visitor::m_current_search_depth to parent class to make it available in TABLE_SHARE::visit_subgraph(). Added MDL_wait_for_graph_visitor::abort_traversal() method which allows to abort traversal of a wait-for graph and report a deadlock. @ sql/sql_base.cc Added dd_owns_lock_open counter and mutex protecting it to track number of connections which do deadlock detection and own or try to acquire LOCK_open. @ sql/sql_base.h Added dd_owns_lock_open counter and mutex protecting it to track number of connections which do deadlock detection and own or try to acquire LOCK_open. @ sql/table.cc Workaround bug #56405 but not allowing MDL deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead report deadlock.
[10 Sep 2010 14:35]
Dmitry Lenev
Hello! I have reported a follow-up for this report - bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors".
[10 Sep 2010 18:51]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:joerg@mysql.com-20100910184813-csdto6tk4nlogrsq) (version source revid:joerg@mysql.com-20100910184813-csdto6tk4nlogrsq) (merge vers: 5.5.7-rc) (pib:21)
[13 Sep 2010 13:49]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:dlenev@mysql.com-20100913103627-p2oqplu42x1gv2bd) (version source revid:dlenev@mysql.com-20100913102657-ye9uwx05td7h2q3o) (merge vers: 5.6.1-m4) (pib:21)
[13 Sep 2010 13:51]
Bugs System
Pushed into mysql-next-mr (revid:dlenev@mysql.com-20100913121556-sfxqlpj9kbc28kaf) (version source revid:dlenev@mysql.com-20100913121556-sfxqlpj9kbc28kaf) (pib:21)
[23 Sep 2010 20:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/118974 3142 Dmitry Lenev 2010-09-23 A better fix for bug #56405 "Deadlock in the MDL deadlock detector", which doesn't introduce bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors". Deadlock could have occurred when workload containing mix of DML, DDL and FLUSH TABLES statements affecting same set of tables was executed in heavily concurrent environment. This deadlock occurred when several connections tried to perform deadlock detection in metadata locking subsystem. The first connection started traversing wait-for graph, encountered sub-graph representing wait for flush, acquired LOCK_open and dived into sub-graph inspection. When it has encountered sub-graph corresponding to wait for metadata lock and blocked while trying to acquire rd-lock on MDL_lock::m_rwlock protecting this subgraph, since some other thread had wr-lock on it. When this wr-lock was released it could have happened (if there was other pending wr-lock against this rwlock) that rd-lock from the first connection was left unsatisfied but at the same time new rd-lock request from the second connection sneaked in and was satisfied (for this to be possible second rd- request should come exactly after wr-lock is released but before pending wr-lock manages to grab rwlock, which is possible both on Linux and in our own rwlock implementation). If this second connection continued traversing wait-for graph and encountered sub-graph representing wait for flush it tried to acquire LOCK_open and thus deadlock was created. The previous patch tried to workaround this problem by not allowing deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead deadlock was reported. As result it has introduced bug #56715. This patch solves this problem in a different way. It introduces a new rw_pr_lock_t implementation to be used by MDL subsystem instead of one based on Linux rwlocks or our own rwlock implementation. This new implementation never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock. Thus situation which has caused this bug becomes impossible with it. Due to fact that this implementation is optimized for wr-lock/unlock scenario which is most common in MDL subsystem it doesn't introduce noticiable performance regressions in sysbench tests. Moreover it significantly improves situation for POINT_SELECT test when many connections are used. No test case is provided as this bug is very hard to repeat in MTR environment but is repeatable with the help of RQG tests. This patch also doesn't include test for bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors" as it takes too much time to be run as part of normal test-suite runs. QQ: Should we also remove support for preferring readers from my_rw_lock_t implementation? @ config.h.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.in We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ include/my_pthread.h Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. @ mysys/thr_rwlock.c Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used.
[24 Sep 2010 23:13]
Paul DuBois
Noted in 5.5.7, 5.6.1 changelogs. Deadlock could occur for heavily concurrent workloads consisting of a mix of DML, DDL, and FLUSH TABLES statements affecting the same set of tables.
[27 Sep 2010 7:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119140 3142 Dmitry Lenev 2010-09-27 A better fix for bug #56405 "Deadlock in the MDL deadlock detector", which doesn't introduce bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors". Deadlock could have occurred when workload containing mix of DML, DDL and FLUSH TABLES statements affecting same set of tables was executed in heavily concurrent environment. This deadlock occurred when several connections tried to perform deadlock detection in metadata locking subsystem. The first connection started traversing wait-for graph, encountered sub-graph representing wait for flush, acquired LOCK_open and dived into sub-graph inspection. When it has encountered sub-graph corresponding to wait for metadata lock and blocked while trying to acquire rd-lock on MDL_lock::m_rwlock protecting this subgraph, since some other thread had wr-lock on it. When this wr-lock was released it could have happened (if there was other pending wr-lock against this rwlock) that rd-lock from the first connection was left unsatisfied but at the same time new rd-lock request from the second connection sneaked in and was satisfied (for this to be possible second rd- request should come exactly after wr-lock is released but before pending wr-lock manages to grab rwlock, which is possible both on Linux and in our own rwlock implementation). If this second connection continued traversing wait-for graph and encountered sub-graph representing wait for flush it tried to acquire LOCK_open and thus deadlock was created. The previous patch tried to workaround this problem by not allowing deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead deadlock was reported. As result it has introduced bug #56715. This patch solves this problem in a different way. It introduces a new rw_pr_lock_t implementation to be used by MDL subsystem instead of one based on Linux rwlocks or our own rwlock implementation. This new implementation never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock. Thus situation which has caused this bug becomes impossible with it. Due to fact that this implementation is optimized for wr-lock/unlock scenario which is most common in MDL subsystem it doesn't introduce noticiable performance regressions in sysbench tests. Moreover it significantly improves situation for POINT_SELECT test when many connections are used. No test case is provided as this bug is very hard to repeat in MTR environment but is repeatable with the help of RQG tests. This patch also doesn't include test for bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors" as it takes too much time to be run as part of normal test-suite runs. QQ: Should we also remove support for preferring readers from my_rw_lock_t implementation? @ config.h.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.in We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ include/my_pthread.h Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. As part of this change removed try-lock part of API for this type of lock. It is not used in our code and it would be hard to implement correctly within constraints of new implementation. @ include/mysql/psi/mysql_thread.h Removed try-lock part of prlock API. It is not used in our code and it would be hard to implement correctly within constraints of new prlock implementation. @ mysys/thr_rwlock.c Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used.
[27 Sep 2010 9:48]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119154 3142 Dmitry Lenev 2010-09-27 A better fix for bug #56405 "Deadlock in the MDL deadlock detector", which doesn't introduce bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors". Deadlock could have occurred when workload containing mix of DML, DDL and FLUSH TABLES statements affecting same set of tables was executed in heavily concurrent environment. This deadlock occurred when several connections tried to perform deadlock detection in metadata locking subsystem. The first connection started traversing wait-for graph, encountered sub-graph representing wait for flush, acquired LOCK_open and dived into sub-graph inspection. When it has encountered sub-graph corresponding to wait for metadata lock and blocked while trying to acquire rd-lock on MDL_lock::m_rwlock protecting this subgraph, since some other thread had wr-lock on it. When this wr-lock was released it could have happened (if there was other pending wr-lock against this rwlock) that rd-lock from the first connection was left unsatisfied but at the same time new rd-lock request from the second connection sneaked in and was satisfied (for this to be possible second rd- request should come exactly after wr-lock is released but before pending wr-lock manages to grab rwlock, which is possible both on Linux and in our own rwlock implementation). If this second connection continued traversing wait-for graph and encountered sub-graph representing wait for flush it tried to acquire LOCK_open and thus deadlock was created. The previous patch tried to workaround this problem by not allowing deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead deadlock was reported. As result it has introduced bug #56715. This patch solves this problem in a different way. It introduces a new rw_pr_lock_t implementation to be used by MDL subsystem instead of one based on Linux rwlocks or our own rwlock implementation. This new implementation never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock. Thus situation which has caused this bug becomes impossible with it. Due to fact that this implementation is optimized for wr-lock/unlock scenario which is most common in MDL subsystem it doesn't introduce noticiable performance regressions in sysbench tests. Moreover it significantly improves situation for POINT_SELECT test when many connections are used. No test case is provided as this bug is very hard to repeat in MTR environment but is repeatable with the help of RQG tests. This patch also doesn't include test for bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors" as it takes too much time to be run as part of normal test-suite runs. @ config.h.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.in We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ include/my_pthread.h Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. As part of this change removed try-lock part of API for this type of lock. It is not used in our code and it would be hard to implement correctly within constraints of new implementation. Finally, removed support of preferring readers from my_rw_lock_t implementation as the only user of this feature was old rw_pr_lock_t implementation. @ include/mysql/psi/mysql_thread.h Removed try-lock part of prlock API. It is not used in our code and it would be hard to implement correctly within constraints of new prlock implementation. @ mysys/thr_rwlock.c Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. Also removed support of preferring readers from my_rw_lock_t implementation as the only user of this feature was old rw_pr_lock_t implementation.
[29 Sep 2010 12:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119406 3148 Dmitry Lenev 2010-09-29 A better fix for bug #56405 "Deadlock in the MDL deadlock detector" that doesn't introduce bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors". Deadlock could have occurred when workload containing a mix of DML, DDL and FLUSH TABLES statements affecting the same set of tables was executed in a heavily concurrent environment. This deadlock occurred when several connections tried to perform deadlock detection in the metadata locking subsystem. The first connection started traversing wait-for graph, encountered a sub-graph representing a wait for flush, acquired LOCK_open and dived into sub-graph inspection. Then it encountered sub-graph corresponding to wait for metadata lock and blocked while trying to acquire a rd-lock on MDL_lock::m_rwlock, since some,other thread had a wr-lock on it. When this wr-lock was released it could have happened (if there was another pending wr-lock against this rwlock) that the rd-lock from the first connection was left unsatisfied but at the same time the new rd-lock request from the second connection sneaked in and was satisfied (for this to be possible the second rd-request should come exactly after the wr-lock is released but before pending the wr-lock manages to grab rwlock, which is possible both on Linux and in our own rwlock implementation). If this second connection continued traversing the wait-for graph and encountered a sub-graph representing a wait for flush it tried to acquire LOCK_open and thus the deadlock was created. The previous patch tried to workaround this problem by not allowing the deadlock detector to lock LOCK_open mutex if some other thread doing deadlock detection already owns it and current search depth is greater than 0. Instead deadlock was reported. As a result it has introduced bug #56715. This patch solves this problem in a different way. It introduces a new rw_pr_lock_t implementation to be used by MDL subsystem instead of one based on Linux rwlocks or our own rwlock implementation. This new implementation never allows situation in which an rwlock is rd-locked and there is a blocked pending rd-lock. Thus the situation which has caused this bug becomes impossible with this implementation. Due to fact that this implementation is optimized for wr-lock/unlock scenario which is most common in the MDL subsystem it doesn't introduce noticeable performance regressions in sysbench tests. Moreover it significantly improves situation for POINT_SELECT test when many connections are used. No test case is provided as this bug is very hard to repeat in MTR environment but is repeatable with the help of RQG tests. This patch also doesn't include a test for bug #56715 "Concurrent transactions + FLUSH result in sporadical unwarranted deadlock errors" as it takes too much time to be run as part of normal test-suite runs. @ config.h.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.cmake We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ configure.in We no longer need to check for presence of pthread_rwlockattr_setkind_np as we no longer use Linux-specific implementation of rw_pr_lock_t which uses this function. @ include/my_pthread.h Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. As part of this change removed try-lock part of API for this type of lock. It is not used in our code and it would be hard to implement correctly within constraints of new implementation. Finally, removed support of preferring readers from my_rw_lock_t implementation as the only user of this feature was old rw_pr_lock_t implementation. @ include/mysql/psi/mysql_thread.h Removed try-lock part of prlock API. It is not used in our code and it would be hard to implement correctly within constraints of new prlock implementation. @ mysys/thr_rwlock.c Introduced new implementation of rw_pr_lock_t. Since it never allows situation in which rwlock is rd-locked and there is a blocked pending rd-lock it is not affected by bug #56405 "Deadlock in the MDL deadlock detector". This implementation is also optimized for wr-lock/unlock scenario which is most common in MDL subsystem. So it doesn't introduce noticiable performance regressions in sysbench tests (compared to old Linux-specific implementation). Moreover it significantly improves situation for POINT_SELECT test when many connections are used. Also removed support of preferring readers from my_rw_lock_t implementation as the only user of this feature was old rw_pr_lock_t implementation.
[30 Sep 2010 13:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119541 3150 Dmitry Lenev 2010-09-30 Reverted a temporary workaround for bug #56405 "Deadlock in the MDL deadlock detector". It is no longer needed as a better fix for this bug has been pushed.
[3 Oct 2010 18:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119786 3220 Vladislav Vaintroub 2010-10-03 A follow-up to the patch for bug #56405 "Deadlock in the MDL deadlock detector". This patch addresses performance regression in OLTP_RO/MyISAM test on Windows introduced by the fix for bug #56405. Thus it makes original patch acceptable as a solution for bug #56585 "Slowdown of readonly sysbench benchmarks (e.g point_select) on Windows 5.5". With this patch, MySQL will use native Windows condition variables and reader-writer locks if they are supported by the OS. This speeds up MyISAM and the effect comes mostly from using native rwlocks. Native conditions improve scalability with higher number of concurrent users in other situations, e.g for prlocks. Benchmark numbers for this patch as measured on Win2008R2 quad core machine are attached to the bug report. ( direct link http://bugs.mysql.com/file.php?id=15883 ) Note, that currently we require at least Windows7/WS2008R2 for reader-writer locks, even if native rwlock is available also on Vista. Reason is that "trylock" APIs are missing on Vista, and trylock is used in the server (in a single place in query cache). While this patch could have been written differently, to enable the native rwlock optimization also on Vista/WS2008 (e.g using native locks everywhere but portable implemenetation in query cache), this would come at the expense of the code clarity, as it would introduce a bew "tryable" rwlock type, to handle Vista case. Another way to improve performance for the special case (OLTP_RO/MYISAM/Vista) would be to eliminate "trylock" usage from server, but this is outside of the scope here.
[4 Oct 2010 0:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119787 3220 Vladislav Vaintroub 2010-10-04 A follow-up to the patch for bug #56405 "Deadlock in the MDL deadlock detector". This patch addresses performance regression in OLTP_RO/MyISAM test on Windows introduced by the fix for bug #56405. Thus it makes original patch acceptable as a solution for bug #56585 "Slowdown of readonly sysbench benchmarks (e.g point_select) on Windows 5.5". With this patch, MySQL will use native Windows condition variables and reader-writer locks if they are supported by the OS. This speeds up MyISAM and the effect comes mostly from using native rwlocks. Native conditions improve scalability with higher number of concurrent users in other situations, e.g for prlocks. Benchmark numbers for this patch as measured on Win2008R2 quad core machine are attached to the bug report. ( direct link http://bugs.mysql.com/file.php?id=15883 ) Note that currently we require at least Windows7/WS2008R2 for reader-writer locks, even though native rwlock is available also on Vista. Reason is that "trylock" APIs are missing on Vista, and trylock is used in the server (in a single place in query cache). While this patch could have been written differently, to enable the native rwlock optimization also on Vista/WS2008 (e.g using native locks everywhere but portable implementation in query cache), this would come at the expense of the code clarity, as it would introduce a new "try-able" rwlock type, to handle Vista case. Another way to improve performance for the special case (OLTP_RO/MYISAM/Vista) would be to eliminate "trylock" usage from server, but this is outside of the scope here. Native conditions variables are used beginning with Vista though the effect of using condition variables alone is not measurable in this benchmark. But when used together with native rwlocks on Win7, native conditions improve performance in high-concurrency OLTP_RO/MyISAM (128 and more sysbench users).
[4 Oct 2010 11:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119817 3220 Vladislav Vaintroub 2010-10-04 A follow-up to the patch for bug #56405 "Deadlock in the MDL deadlock detector". This patch addresses performance regression in OLTP_RO/MyISAM test on Windows introduced by the fix for bug #56405. Thus it makes original patch acceptable as a solution for bug #56585 "Slowdown of readonly sysbench benchmarks (e.g point_select) on Windows 5.5". With this patch, MySQL will use native Windows condition variables and reader-writer locks if they are supported by the OS. This speeds up MyISAM and the effect comes mostly from using native rwlocks. Native conditions improve scalability with higher number of concurrent users in other situations, e.g for prlocks. Benchmark numbers for this patch as measured on Win2008R2 quad core machine are attached to the bug report. ( direct link http://bugs.mysql.com/file.php?id=15883 ) Note that currently we require at least Windows7/WS2008R2 for reader-writer locks, even though native rwlock is available also on Vista. Reason is that "trylock" APIs are missing on Vista, and trylock is used in the server (in a single place in query cache). While this patch could have been written differently, to enable the native rwlock optimization also on Vista/WS2008 (e.g using native locks everywhere but portable implementation in query cache), this would come at the expense of the code clarity, as it would introduce a new "try-able" rwlock type, to handle Vista case. Another way to improve performance for the special case (OLTP_RO/MYISAM/Vista) would be to eliminate "trylock" usage from server, but this is outside of the scope here. Native conditions variables are used beginning with Vista though the effect of using condition variables alone is not measurable in this benchmark. But when used together with native rwlocks on Win7, native conditions improve performance in high-concurrency OLTP_RO/MyISAM (128 and more sysbench users).
[9 Nov 2010 19:42]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[12 Nov 2010 0:34]
Paul DuBois
Noted in 5.5.7 changelog. Deadlock could occur for a workload consisting of a mix of DML, DDL, and FLUSH TABLES statements affecting the same set of tables in a heavily concurrent environment.
[13 Nov 2010 16:04]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:28]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
Description: During RQG testing on mysql-5.5-runtime, I got a deadlock even with lock_wait_timeout=2. The two most relevant threads are: Thread 42 (Thread 22082): #0 pthread_rwlock_rdlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_rdlock.S:85 #1 0x00000000006c7945 in inline_mysql_prlock_rdlock (that=0x7f6ca4059e30, src_file=0xb76948 "/export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc", src_line=1996) at /export/home/x/mysql-5.5-runtime-rqg/include/mysql/psi/mysql_thread.h:816 #2 0x00000000006ca0fd in MDL_lock::visit_subgraph (this=0x7f6ca4059ca0, waiting_ticket=0x29c8410, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:1996 #3 0x00000000006ca45b in MDL_ticket::accept_visitor (this=0x29c8410, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2136 #4 0x00000000006ca4ca in MDL_context::visit_subgraph (this=0x7f6cac276090, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2163 #5 0x000000000068f6c3 in TABLE_SHARE::visit_subgraph (this=0x7f6ca426fc80, wait_for_flush=0x7f6cbd0f6490, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/table.cc:3117 #6 0x000000000068f583 in Wait_for_flush::accept_visitor (this=0x7f6cbd0f6490, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/table.cc:3051 #7 0x00000000006ca4ca in MDL_context::visit_subgraph (this=0x2708040, gvisitor=0x7f6cbd0f6420) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2163 #8 0x00000000006ca51c in MDL_context::find_deadlock (this=0x2708040) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2194 #9 0x000000000068f85b in TABLE_SHARE::wait_for_old_version (this=0x7f6ca426fc80, thd=0x2707f80, abstime=0x7f6cbd0f6530, deadlock_weight=0) at /export/home/x/mysql-5.5-runtime-rqg/sql/table.cc:3176 #10 0x000000000058dedf in tdc_wait_for_old_version (thd=0x2707f80, db=0x2a79020 "testdb_S", table_name=0x2a79029 "t1_base1_N", wait_timeout=2, deadlock_weight=0) at /export/home/x/mysql-5.5-runtime-rqg/sql/sql_base.cc:2537 #11 0x000000000058ebc5 in open_table (thd=0x2707f80, table_list=0x7f6cac2e8f00, mem_root=0x7f6cbd0f6b30, ot_ctx=0x7f6cbd0f6b70) at /export/home/x/mysql-5.5-runtime-rqg/sql/sql_base.cc:2937 Thread 40 (Thread 22052): #0 __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:136 #1 0x00007f6ccc2dd60f in _L_lock_1172 () from /lib/libpthread.so.0 #2 0x00007f6ccc2dd55a in __pthread_mutex_lock (mutex=0x1265fa8) at pthread_mutex_lock.c:101 #3 0x0000000000a9299d in safe_mutex_lock (mp=0x1265f80, try_lock=0 '\000', file=0xb66b70 "/export/home/x/mysql-5.5-runtime-rqg/sql/table.cc", line=3088) at /export/home/x/mysql-5.5-runtime-rqg/mysys/thr_mutex.c:152 #4 0x0000000000685666 in inline_mysql_mutex_lock (that=0x1265f80, src_file=0xb66b70 "/export/home/x/mysql-5.5-runtime-rqg/sql/table.cc", src_line=3088) at /export/home/x/mysql-5.5-runtime-rqg/include/mysql/psi/mysql_thread.h:645 #5 0x000000000068f5ee in TABLE_SHARE::visit_subgraph (this=0x7f6cac1ccb10, wait_for_flush=0x7f6cba87f490, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/table.cc:3088 #6 0x000000000068f583 in Wait_for_flush::accept_visitor (this=0x7f6cba87f490, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/table.cc:3051 #7 0x00000000006ca4ca in MDL_context::visit_subgraph (this=0x7f6cac1c4060, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2163 #8 0x00000000006ca313 in MDL_lock::visit_subgraph (this=0x7f6ca4059ca0, waiting_ticket=0x7f6ca41234f0, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2094 #9 0x00000000006ca45b in MDL_ticket::accept_visitor (this=0x7f6ca41234f0, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2136 #10 0x00000000006ca4ca in MDL_context::visit_subgraph (this=0x26c4b70, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2163 #11 0x00000000006ca3ab in MDL_lock::visit_subgraph (this=0x7f6ca4059ca0, waiting_ticket=0x7f6cac052090, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2105 #12 0x00000000006ca45b in MDL_ticket::accept_visitor (this=0x7f6cac052090, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2136 #13 0x00000000006ca4ca in MDL_context::visit_subgraph (this=0x277efa0, gvisitor=0x7f6cbaa46260) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2163 #14 0x00000000006ca51c in MDL_context::find_deadlock (this=0x277efa0) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:2194 #15 0x00000000006c995c in MDL_context::acquire_lock (this=0x277efa0, mdl_request=0x28c6228, lock_wait_timeout=2) at /export/home/x/mysql-5.5-runtime-rqg/sql/mdl.cc:1760 Thread 42 is unable to get a rdlock on MDL_lock::m_rwlock for testdb_N.t1_base1_N. It also holds LOCK_open, taken in TABLE_SHARE::visit_subgraph() Thread 40 is unable to lock LOCK_open in TABLE_SHARE::visit_subgraph(). It holds two rdlocks on MDL_lock::m_rwlock for testdb_N.t1_base1_N taken in MDL_lock::visit_subgraph(). Four other threads are trying to get rwlock on the same MDL_lock::m_rwlock. Inspecting the MDL_lock::m_rwlock inside gdb gives: (gdb) p m_rwlock.m_prlock.__data $3 = {__lock = 0, __nr_readers = 2, __readers_wakeup = 3, __writer_wakeup = 14, __nr_readers_queued = 1, __nr_writers_queued = 4, __writer = 0, __shared = 0, __pad1 = 0, __pad2 = 0, __flags = 0} How to repeat: RQG with WL5004_sql.yy grammar using a lot of threads.