From: Jon Olav Hauglid Date: August 23 2010 1:23pm Subject: bzr commit into mysql-5.5-bugfixing branch (jon.hauglid:3117) Bug#54332 List-Archive: http://lists.mysql.com/commits/116503 X-Bug: 54332 Message-Id: <201008231324.o7NDOvKI003688@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5001624956232933044==" --===============5001624956232933044== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-runtime-bug54332/ based on revid:jon.hauglid@stripped 3117 Jon Olav Hauglid 2010-08-23 Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED The problem was that deadlocks involving INSERT DELAYED were not detected. The reason for this is that two threads are involved in INSERT DELAYED: the connection thread and the handler thread. The connection thread would wait while the handler thread acquired locks and opened the table. In essence, this adds an edge to the wait-for-graph between the connection thread and the handler thread that the deadlock detector is unaware of. Therefore any deadlocks involving INSERT DELAYED were not detected. This patch fixes the problem by having the connection thread lock on the table before starting the handler thread. This allows the deadlock detector to detect any possible deadlocks resulting from trying to lock the table. If a lock is successfully acquired, the handler thread is started and given a copy of the ticket representing the lock. The connection thread then releases any locks it has taken during the process before continuing. When the handler thread then tries to lock and open the table, it will find that it already has the lock and therefore not acquire any new locks. Test cases added to delayed.test. modified: mysql-test/r/delayed.result mysql-test/t/delayed.test sql/sql_insert.cc === modified file 'mysql-test/r/delayed.result' --- a/mysql-test/r/delayed.result 2009-12-11 09:39:38 +0000 +++ b/mysql-test/r/delayed.result 2010-08-23 13:23:46 +0000 @@ -345,3 +345,78 @@ CREATE TABLE t1 LIKE t2; ERROR 42S01: Table 't1' already exists DROP TABLE t2; DROP TABLE t1; +# +# Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); +# Test 1: Using LOCK TABLE +# Connection con1 +LOCK TABLE t1 WRITE; +# Connection default +LOCK TABLE t2 WRITE; +# Sending: +INSERT DELAYED INTO t1 VALUES (1); +# Connection con1 +# Wait until INSERT DELAYED is blocked on table 't1'. +INSERT DELAYED INTO t2 VALUES (1); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +UNLOCK TABLES; +# Connection default +# Reaping: INSERT DELAYED INTO t1 VALUES (1) +UNLOCK TABLES; +# Test 2: Using ALTER TABLE +START TRANSACTION; +SELECT * FROM t1 WHERE a=0; +a +# Connection con1 +# Sending: +ALTER TABLE t1 COMMENT 'test'; +# Connection default +# Wait until ALTER TABLE is blocked on table 't1'. +INSERT DELAYED INTO t1 VALUES (3); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +# Connection con1 +# Reaping: ALTER TABLE t1 COMMENT 'test' +# Test 3: Using RENAME TABLE +# Connection default +START TRANSACTION; +INSERT INTO t2 VALUES (1); +# Connection con1 +# Sending: +RENAME TABLE t1 to t5, t2 to t4; +# Connection default +# Wait until RENAME TABLE is blocked on table 't1'. +INSERT DELAYED INTO t1 VALUES (4); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +# Connection con1 +# Reaping: RENAME TABLE t1 to t5, t2 to t4 +# Connection default +# Reverting the renames +RENAME TABLE t5 to t1, t4 to t2; +# Test 4: Two INSERT DELAYED on the same table +START TRANSACTION; +INSERT INTO t2 VALUES (1); +# Connection con2 +LOCK TABLE t1 WRITE, t2 WRITE; +# Connection con1 +# Wait until LOCK TABLE is blocked on table 't2'. +INSERT DELAYED INTO t1 VALUES (5); +# Connection default +# Wait until INSERT DELAYED is blocked on table 't1'. +INSERT DELAYED INTO t1 VALUES (6); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +# Connection con2 +# Reaping: LOCK TABLE t1 WRITE, t2 WRITE +UNLOCK TABLES; +# Connection con1 +# Reaping: INSERT DELAYED INTO t1 VALUES (5) +# Connection con2 +# Connection con1 +# Connection default +DROP TABLE t1, t2, t3; === modified file 'mysql-test/t/delayed.test' --- a/mysql-test/t/delayed.test 2010-08-06 11:29:37 +0000 +++ b/mysql-test/t/delayed.test 2010-08-23 13:23:46 +0000 @@ -388,3 +388,162 @@ CREATE TABLE t1 LIKE t2; DROP TABLE t2; DROP TABLE t1; + + +--echo # +--echo # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); + +--echo # Test 1: Using LOCK TABLE + +--echo # Connection con1 +connect (con1, localhost, root); +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +LOCK TABLE t2 WRITE; +--echo # Sending: +--send INSERT DELAYED INTO t1 VALUES (1) + +--echo # Connection con1 +connection con1; +--echo # Wait until INSERT DELAYED is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "INSERT DELAYED INTO t1 VALUES (1)"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t2 VALUES (1); +UNLOCK TABLES; + +--echo # Connection default +connection default; +--echo # Reaping: INSERT DELAYED INTO t1 VALUES (1) +--reap +UNLOCK TABLES; + +--echo # Test 2: Using ALTER TABLE + +START TRANSACTION; +SELECT * FROM t1 WHERE a=0; + +--echo # Connection con1 +connection con1; +--echo # Sending: +--send ALTER TABLE t1 COMMENT 'test' + +--echo # Connection default +connection default; +--echo # Wait until ALTER TABLE is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "ALTER TABLE t1 COMMENT 'test'"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (3); +COMMIT; + +--echo # Connection con1 +connection con1; +--echo # Reaping: ALTER TABLE t1 COMMENT 'test' +--reap + +--echo # Test 3: Using RENAME TABLE + +--echo # Connection default +connection default; +START TRANSACTION; +INSERT INTO t2 VALUES (1); + +--echo # Connection con1 +connection con1; +--echo # Sending: +--send RENAME TABLE t1 to t5, t2 to t4 + +--echo # Connection default +connection default; +--echo # Wait until RENAME TABLE is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "RENAME TABLE t1 to t5, t2 to t4"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (4); +COMMIT; + +--echo # Connection con1 +connection con1; +--echo # Reaping: RENAME TABLE t1 to t5, t2 to t4 +--reap + +--echo # Connection default +connection default; +--echo # Reverting the renames +RENAME TABLE t5 to t1, t4 to t2; + +--echo # Test 4: Two INSERT DELAYED on the same table + +START TRANSACTION; +INSERT INTO t2 VALUES (1); + +--echo # Connection con2 +connect (con2, localhost, root); +--send LOCK TABLE t1 WRITE, t2 WRITE + +--echo # Connection con1 +connection con1; +--echo # Wait until LOCK TABLE is blocked on table 't2'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "LOCK TABLE t1 WRITE, t2 WRITE"; +--source include/wait_condition.inc +--send INSERT DELAYED INTO t1 VALUES (5) + +--echo # Connection default +connection default; +--echo # Wait until INSERT DELAYED is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "INSERT DELAYED INTO t1 VALUES (5)"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (6); +COMMIT; + +--echo # Connection con2 +connection con2; +--echo # Reaping: LOCK TABLE t1 WRITE, t2 WRITE +--reap +UNLOCK TABLES; + +--echo # Connection con1 +connection con1; +--echo # Reaping: INSERT DELAYED INTO t1 VALUES (5) +--reap + +--echo # Connection con2 +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +--echo # Connection con1 +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc + +--echo # Connection default +connection default; +DROP TABLE t1, t2, t3; === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2010-08-20 08:24:32 +0000 +++ b/sql/sql_insert.cc 2010-08-23 13:23:46 +0000 @@ -548,8 +548,36 @@ bool open_and_lock_for_insert_delayed(TH DBUG_RETURN(TRUE); } + /* + In order for the deadlock detector to be able to find any deadlocks + caused by locking this table, we take the lock inside the connection + thread. If this goes ok, the ticket is cloned and added to the list + of granted locks held by the handler thread. + */ + MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint(); + if (thd->mdl_context.acquire_lock(&table_list->mdl_request, + thd->variables.lock_wait_timeout)) + /* + If a lock can't be acquired, it makes no sense to try normal insert. + Therefore we just abort the statement. + */ + DBUG_RETURN(TRUE); + + /* + If a lock was acquired above, we should release it after delayed_get_table() + has cloned the ticket for the handler thread. Note that acquire_lock() can + succeed because of a lock alread held by the connection. In this case we + should not release it here. + */ + MDL_ticket *table_ticket = mdl_savepoint == thd->mdl_context.mdl_savepoint() ? + NULL: thd->mdl_context.mdl_savepoint(); + if (delayed_get_table(thd, table_list)) + { + if (table_ticket) + thd->mdl_context.release_lock(table_ticket); DBUG_RETURN(TRUE); + } if (table_list->table) { @@ -560,6 +588,8 @@ bool open_and_lock_for_insert_delayed(TH if (open_and_lock_tables(thd, table_list->next_global, TRUE, 0)) { end_delayed_insert(thd); + if (table_ticket) + thd->mdl_context.release_lock(table_ticket); DBUG_RETURN(TRUE); } /* @@ -568,8 +598,13 @@ bool open_and_lock_for_insert_delayed(TH */ if (!table_list->derived && !table_list->view) table_list->updatable= 1; // usual table + if (table_ticket) + thd->mdl_context.release_lock(table_ticket); DBUG_RETURN(FALSE); } + if (table_ticket) + thd->mdl_context.release_lock(table_ticket); + table_list->mdl_request.ticket= NULL; #endif /* * This is embedded library and we don't have auxiliary @@ -2025,6 +2060,19 @@ bool delayed_get_table(THD *thd, TABLE_L /* Replace volatile strings with local copies */ di->table_list.alias= di->table_list.table_name= di->thd.query(); di->table_list.db= di->thd.db; + + /* + Clone the ticket representing the lock on the target table for + the insert and add it to the list of granted locks held by + the handler thread. + */ + if (di->thd.mdl_context.clone_ticket(&table_list->mdl_request)) + { + delete di; + my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR)); + goto end_create; + } + di->lock(); mysql_mutex_lock(&di->mutex); if ((error= mysql_thread_create(key_thread_delayed_insert, --===============5001624956232933044== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-runtime-bug54332/ # testament_sha1: 473f6bf89335a2378063d1362c658b0c6319360e # timestamp: 2010-08-23 15:23:49 +0200 # base_revision_id: jon.hauglid@stripped\ # 7d3mk3y33mm7ogbk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXQXnhQAB//fgFQwXf////// 3+C////6YBBL477fXpvd27dfR7vnDJKBz54oB6Pa2nMeuvQ9Vo1y7tyoOl29PU83DSKZpADJoAAA AAAAAAAACSgBTAExBTT0pmhPKbKBoGmIaaAAyNPSCUBE0EnqjINGm0gaAA0PUAAAAeoAJCSIk9Hp TT1ND1PSGENBkGmjQAAA0AAIpCCnk1NpqntJoRqeUzU8p5Q9QMmj1MNTTR6QAyNqCRImgJpoaCaY UwQBTYQTRoNBppoaAAmCIkx9ixPx4BmqKDAgsc46GMslD0JoMEGLvrShVDCM3jzxcbMGJx/Xr9f6 7DHqs2+HvnZm/jj7NfZBmzPY2qcVtgxvbl0zKYeSLz4I0o44D2+7l3a6L8GuuyFi1a5xe63Dm2d7 kSHle72B3tIogYMUBffbfL4FVIaV4RjaUnUuCnCO3DGSLfs3NjY5BBqi6kUsIVs+2NLDxqV8tGM0 ETDFQFLwN+BFkpPaXX9LPHNttcDqTTuVe5Lbctd/JnLEVq1SBW1VggBiDiE50E5qkUEfcj7hI7zY aih+MmihgTGeS8y3pppga/2RzdvHfk5CJB2v0PPiIZb7qliEIe8GYqAx4iHEKkt+5G1HVFo950Kc jajwNCN6IAQxJPCtcwfYRR8B7egqmMYVhgWnDpF0gFwMOggSLbSS1ns5131FhZYZgrUMXq08oiP4 jhuQyEQlCJDIGDQaRVAYBYBmG9NpoVyICBNsuEYbYcY0aMrNyVvSm7P8yVHVQjup2meQ8a2EH/Sp vXRVTFARlAVbhLIu4qYEtICTvMfNQmokGOTa28ETnGdGBCYUuKIJyH2MdkOIJc0fgiOWEWGOwrGx q62ulc4jpU4TNemUNziUhGoGiUX+PplDppN6ulVo7RAhhkKxtbFkpdcFLVohYLrwwLjB2eVDkS4g DkEEt4vIWFiRA0pDIGQmMgaKy8RfoRK5mbh4CcYjw5mRoR1wlVVC2qcoOnYUm4axSKMxk1myWtHj 4P2Uc1jbG0UrCK4qC5+s9R0R1RXjYVhff7wgOvRKEQUVSOFRBDQBAQR1wchRrLL3FWQqitUqvC9Q TQwVqFOO+vMRQSfybG0DQNNMBgNjGMY2mm222xsY22aRo+0yCh5mhuOlSSjhyiGe5ExI3/L8ZCVA dvHVouUORP6H9CKE5VHzzzmYylpaErL8ni7sA7vSV1BoLIeTyuGSV7HO423hJRDNBwPGEc5LiABp 3jYHiAPlPQqAPXgbTU6A9SVKzjx5J2mR08IlqThOqFquOi/SdVRfERp50g5qTSmmNsXx0Y42pZne rJWwhNEqnxpASWOYOp3d0KzdcuqaWcY0S6MbQRviTm+IeIVGYFSSmIIGoIB0Br2SDbUTVVCRF5eU nSKwCxyOZQ33omvYdxXLA5COEcTVmAwwgRBwGYvOJIkhiSoDA8kikCkp/HQgPi0Slj99Dn47t283 Qi7GpVhZW3dTeTgSqLchF7dEMIea7QjaEOiTl3MW2fVnHwUoSBptmb0F605jFRK9DfmZxFkZWmgV LrpxxMt2+661UBFzI2BydGkoV+dxEbu3TJKgi0RSZKpnGyKn46BGVgXdvCOVgy07bKhay5tjOF4P OAbD24QxgZ+4nuuLCPIoXHAzEUT05oiwvH1EA17ymIsdxibJTbeQ58Ru7Y3i0yJFcjfqzykloXPv nZmdFfjnBVBC4GkNiKc2QbivWD808kjlpxtOJKwnwZUiAXtxsNYkby6Hk3yGlsNknVDtqGogcd8F 3xmF0bmdvmXczDm110qC8tbYD2qwYO4LaQuh7qzrNxia1118MDulWikWwkzebRGGBXBUxYDx6s52 55x00RVlWxmZfMZgNIiO92HWZUhlO1pYcrR/T9bjqMYaqupxYVmbsSBGdqNSV3Ordr5bVImUtupO u2BkGc7yTUlUQwkMrw0lW+jyTnX0KDywU8C4dUuK32rI2gqceCXoOZrYyLBpE8ClQUDMgcvNbxYG l14UX4KsiYshDILRYColKrt6jV3lucld6pjwKWGzFivcMUld2alxLaWGy447nG26u66LVSbCzDwA 5BzC67phwhsdxDSWN8ENVY0y541lxFm75XxcqNOy1Xd3gRzHMJVrdq9wpsUonWNY/faPCK5G4wEH 8Aj4qvHcXekVklgwkDUtg9tAEmiYJCHn7goK1h6i0Jqw47BtssEGhgzTKE0NCVfuKCMoWHGi9wrQ VzWpS1LEPct4W1iBeH2aBT21DcVF3+mGNFz3Pes32sPDpSTKSSQgIBdQ9xoHAXtr3rJSlw5CuRvi vkFSF4JDqN8LVxvSTwjmFqrWZrUTU1JtgaKUykOClaaBgdogTSaSBcrDMU1CuFy5KWDJCSb9LNAF 6Ebg3zWwalyybTaLZ4F7wYJcqGodEkvBgUkQusbyiSECcxxKDRN1bQEkpQaAjbiM2QwpqQsGsUUF nWXYzAvYx73DMLhI3XFQsxaXWkJZopeYiuz9gnoDcjX7+gTyoeKDtl00fC1MKyk+n1SSpKAbCsST zyMuEc0qTpSLgPoNHV5z0EHWfSXH3Hs8mhQpCB3r+uh2dqsvzhKkzALUmEkBQt9A+Mxdgj9EkxFD QyPkuoqF6JRezY0DVBAoRpRDEZy0a0Fn1yIE7BustYNmMkPuonJEGPF9EUk4yAX30OfBEcHAdfpM ZNPSeoJHs4Pi75YZm4bRnwetwRlAj+RFQkHg1nZRNK8wlfuZieB+/sLjyIqNJMg0mAdP0W3nkoPv LPJLVKl0QlfOcle7kmCCWLshBpCsIECSVw0liyQNkxDohWIfOJvF4TkIRELNCC1ZkEQGJAPQaxkr awpF9oQhJZsqDvcVMRYjplEkGEhtoNVQoTKaWTTmbSzGMzrDtMEdxejEp2VfTttEiWOpHgewj0NU 5Jd52JZjAWGsbIP89bZGxrHlHqDUK2hzJCM9SAzkgvErVpjX1nLYb/W+gxHcjtNpKYoaibCEHRJz 6lXElCEhXAhmzFR1ror185B9+91vgw6rYwwIXIPUW3+EmaiWOHhXSDf6ynesV6fNOFGlXCayMAjX AkwDXk+hYCa/UJUsjbxoJaYQQQKA1Nxhfw4UobhFZgTJh7UqPlv7rvfe+znKhgAt5ZixjLvFHHqZ y9jTYBwVEm7hyNNLnSZ4WI30N4t3oFS+dXRqC9QiUI8JI0Y1oH5pcDEK7sQ9EI0YGgkavL8PRk9R 1szgX1INYshpsqoBYi9HkDGxDmiW0XS7p4looxEXgFsCy+1NPzeYUvVhPu8Tw7vm7CD4lKy82Eqh CdgrMQkc/+L0rmDftNwoQbWREN6yIHJHCVs5zT2LxASEsI2KoAzrn6XNivCPDKwA8N1Pu2bJD0Hr 5m/jtT0jzAtQfOypHAkpELcY2hDbYlnpYkVwvrBa+nUjTfYogR6i821AEG9CjizpGRtpmS21oC6n FKElJ0YREfdhmNbUFIguC8bBbXBQkapMSgchYuEaTYM6plopNZMr96IDrkLkIxLzangj5WeHTKim CAyYm0MGBglLMYhtpjehGoW0RIXekNiVDcoRQR7fye3hoV3y949QfefSDUZ9+rjNsAOYzKuIgbrz apBDuopAZdAputBvZtO4RZsXwQZA6CpvnNqEFZRQB+yYHFphkJrYGIHQrIZC0WCsSOJJ6DgAIz3U jUzvLrZzCoblgYYSsYTlvElSGCIj14OrcOouSFqJxUlw7mdKteybYB4HFRJGPoQcvqzYicL+cTcx Ta052xQyrrV7FZ0DZzC2Q/3xFDyx7fhKVIvHgxCTrv06iQxjcaIioZ2AlgIaAnkmT3liUq4ScIZD G1MIFJKgiaBm4RCJwRZUpcQiMKdBkYlgiFY7emqjeF1wkEQyJoaEZkoaQkLRPm2WoJCYna0xGTQE PfgaxiaBJ46T3s9K71sRLNV9RBkndAi81Ysl5FYGoUIFgTAOmS8ogYDExgDQE0xFqrHGeuQOAHEc 0lIVUkbWB58qk95UHafwfn4caFwZpc8M+aOVSD0GrvaaHMMVjWkfWI9xgjZZ3ylbBNelF9KX35G2 43Zme3YA7o+c7y3Uu1KrEUh7WBCByZ7JKEI7MQwWtKZzdtcOIIkaQ4sE/OjhjUFFI4A1RAwtWQ0H pm7xtCUsBjZ4X3oMomdgEd1RR/JF4rmFrbGEa3ahjRt9DobK4g89Uo4TBVZKKA3QiGpj4xMm8i7t rsDxu1mtGtooF2BnbFGDp4opI1miRHwiHkmFS8VORKn5itFJe9ijSSMNRLfW+225wEhB7RmDXaNQ TA1nGBQyJo1jBizFnEg9M9RpFcAW0R4DMqgI2xVPA01+1GJpdUV1Bph0issoJagk2RQ2HYLhkkaK bqy2f1wmBGSEGgBiExgLsONwGFuOAiFbcJ1TVSPfcC8mLFh/4fx+oC1Ei5sFcXDTLGeFBy0msKvD 8Hx7EhzLENIy6DSzTVD5euOkvpa7GgqYNiRDEE2HzZbuKmDQac8kKXU0pqqqIGM0QKCXJJuuMJSQ Zu+NjKaSgS2SW71A4t8tOXBZlfF338Hq1AGFF0mLmVHEUGgFqYAwyiGAjM2wjBLFp1O7HY8X59e7 apIkQ8jBzhVkF4BE+wRoODMloV55+NHuRq0uaPF52kfEuEfYTCe83bfdjqILQCDYdKbjlymSJfCI BxTdSs7IEIOG4PcZBwGGWOk6Oyq/8XckU4UJB0F54UA= --===============5001624956232933044==--