Skip to content

Commit 8abf664

Browse files
author
Jon Olav Hauglid
committed
Bug #45225 Locking: hang if drop table with no timeout
This patch introduces timeouts for metadata locks. The timeout is specified in seconds using the new dynamic system variable "lock_wait_timeout" which has both GLOBAL and SESSION scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). The default value is 1 year. The new server parameter "lock-wait-timeout" can be used to set the default value parameter upon server startup. "lock_wait_timeout" applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH READ LOCK and HANDLER statements. The patch also changes thr_lock.c code (table data locks used by MyISAM and other simplistic engines) to use the same system variable. InnoDB row locks are unaffected. One exception to the handling of the "lock_wait_timeout" variable is delayed inserts. All delayed inserts are executed with a timeout of 1 year regardless of the setting for the global variable. As the connection issuing the delayed insert gets no notification of delayed insert timeouts, we want to avoid unnecessary timeouts. It's important to note that the timeout value is used for each lock acquired and that one statement can take more than one lock. A statement can therefore block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported. Test case added to lock_multi.test. --BZR-- revision-id: [email protected] property-branch-nick: mysql-next-4284-bug45225 property-file-info: ld7:file_id65:sp1f-my_pthread.h-19700101030959-z4yp3kljwx5fgmhlyvumtwxuw73xgrjn7:message48:Added macros for comparing two timespec structs.4:path20:include/my_pthread.hed7:file_id63:sp1f-thr_lock.h-19700101030959-kno333bw76cie6vypbxrrtnk53bficcp7:message41:Introduced timeouts for thr_lock.c locks.4:path18:include/thr_lock.hed7:file_id51:mysqldhelp.result-20091210121810-wnsq93ex4gn8t8su-17:message49:Updated result file with the new server variable.4:path39:mysql-test/r/mysqld--help-notwin.resulted7:file_id54:mysqldhelpwin.result-20091213190821-0x6wvi3s2b38lows-17:message49:Updated result file with the new server variable.4:path36:mysql-test/r/mysqld--help-win.resulted7:file_id54:lock_wait_timeout_ba-20100208140345-24x54ge3x455sx40-17:message45:Added basic test for the new server variable.4:path58:mysql-test/suite/sys_vars/r/lock_wait_timeout_basic.resulted7:file_id54:lock_wait_timeout_ba-20100208140352-v1b34b6b3kvlvuc6-17:message45:Added basic test for the new server variable.4:path56:mysql-test/suite/sys_vars/t/lock_wait_timeout_basic.tested7:file_id63:sp1f-thr_lock.c-19700101030959-igvxgo25qd7i2moc4pgo5eoth3xp34mh7:message41:Introduced timeouts for thr_lock.c locks.4:path16:mysys/thr_lock.ced7:file_id40:mdl.cc-20080523121737-j62pi0m62eaw1hq6-17:message39:Introduced timeouts for metadata locks.4:path10:sql/mdl.cced7:file_id39:mdl.h-20080523121748-o4y2wcq3maotb9do-17:message39:Introduced timeouts for metadata locks.4:path9:sql/mdl.hed7:file_id64:sp1f-sql_base.cc-19700101030959-w7tul2gb2n4jzayjwlslj3ybmf3uhk6a7:message51:Introduced timeouts in tdc_wait_for_old_versions().4:path15:sql/sql_base.cced7:file_id64:sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s7:message44:Added new server variable lock_wait_timeout.4:path15:sql/sql_class.hed7:file_id48:set_var_new.cc-20090803110928-jyk836zs40b2z8e3-17:message44:Added new server variable lock_wait_timeout.4:path15:sql/sys_vars.ccee testament3-sha1: 011bc4845a6f96a157a542448ce6175fc40cdb23
1 parent 714a054 commit 8abf664

20 files changed

Lines changed: 855 additions & 88 deletions

‎.bzrfileids‎

226 Bytes
Binary file not shown.

‎include/my_pthread.h‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,19 @@ struct timespec {
102102
(ABSTIME).max_timeout_msec= (long)((NSEC)/1000000); \
103103
}
104104

105+
/**
106+
Compare two timespec structs.
107+
108+
@retval 1 If TS1 ends after TS2.
109+
110+
@retval 0 If TS1 is equal to TS2.
111+
112+
@retval -1 If TS1 ends before TS2.
113+
*/
114+
#define cmp_timespec(TS1, TS2) \
115+
((TS1.tv.i64 > TS2.tv.i64) ? 1 : \
116+
((TS1.tv.i64 < TS2.tv.i64) ? -1 : 0))
117+
105118

106119
int win_pthread_mutex_trylock(pthread_mutex_t *mutex);
107120
int pthread_create(pthread_t *, const pthread_attr_t *, pthread_handler, void *);
@@ -412,6 +425,33 @@ int my_pthread_mutex_trylock(pthread_mutex_t *mutex);
412425
(ABSTIME).tv_nsec= (long) (now % ULL(10000000) * 100 + ((NSEC) % 100)); \
413426
}
414427
#endif /* !set_timespec_nsec */
428+
#endif /* HAVE_TIMESPEC_TS_SEC */
429+
430+
/**
431+
Compare two timespec structs.
432+
433+
@retval 1 If TS1 ends after TS2.
434+
435+
@retval 0 If TS1 is equal to TS2.
436+
437+
@retval -1 If TS1 ends before TS2.
438+
*/
439+
#ifdef HAVE_TIMESPEC_TS_SEC
440+
#ifndef cmp_timespec
441+
#define cmp_timespec(TS1, TS2) \
442+
((TS1.ts_sec > TS2.ts_sec || \
443+
(TS1.ts_sec == TS2.ts_sec && TS1.ts_nsec > TS2.ts_nsec)) ? 1 : \
444+
((TS1.ts_sec < TS2.ts_sec || \
445+
(TS1.ts_sec == TS2.ts_sec && TS1.ts_nsec < TS2.ts_nsec)) ? -1 : 0))
446+
#endif /* !cmp_timespec */
447+
#else
448+
#ifndef cmp_timespec
449+
#define cmp_timespec(TS1, TS2) \
450+
((TS1.tv_sec > TS2.tv_sec || \
451+
(TS1.tv_sec == TS2.tv_sec && TS1.tv_nsec > TS2.tv_nsec)) ? 1 : \
452+
((TS1.tv_sec < TS2.tv_sec || \
453+
(TS1.tv_sec == TS2.tv_sec && TS1.tv_nsec < TS2.tv_nsec)) ? -1 : 0))
454+
#endif /* !cmp_timespec */
415455
#endif /* HAVE_TIMESPEC_TS_SEC */
416456

417457
/* safe_mutex adds checking to mutex for easier debugging */

‎include/thr_lock.h‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -156,21 +156,25 @@ void thr_lock_data_init(THR_LOCK *lock,THR_LOCK_DATA *data,
156156
void *status_param);
157157
enum enum_thr_lock_result thr_lock(THR_LOCK_DATA *data,
158158
THR_LOCK_OWNER *owner,
159-
enum thr_lock_type lock_type);
159+
enum thr_lock_type lock_type,
160+
ulong lock_wait_timeout);
160161
void thr_unlock(THR_LOCK_DATA *data);
161162
enum enum_thr_lock_result thr_multi_lock(THR_LOCK_DATA **data,
162-
uint count, THR_LOCK_OWNER *owner);
163+
uint count, THR_LOCK_OWNER *owner,
164+
ulong lock_wait_timeout);
163165
void thr_multi_unlock(THR_LOCK_DATA **data,uint count);
164166
void
165167
thr_lock_merge_status(THR_LOCK_DATA **data, uint count);
166168
void thr_abort_locks(THR_LOCK *lock, my_bool upgrade_lock);
167169
my_bool thr_abort_locks_for_thread(THR_LOCK *lock, my_thread_id thread);
168170
void thr_print_locks(void); /* For debugging */
169171
my_bool thr_upgrade_write_delay_lock(THR_LOCK_DATA *data,
170-
enum thr_lock_type new_lock_type);
172+
enum thr_lock_type new_lock_type,
173+
ulong lock_wait_timeout);
171174
void thr_downgrade_write_lock(THR_LOCK_DATA *data,
172175
enum thr_lock_type new_lock_type);
173-
my_bool thr_reschedule_write_lock(THR_LOCK_DATA *data);
176+
my_bool thr_reschedule_write_lock(THR_LOCK_DATA *data,
177+
ulong lock_wait_timeout);
174178
#ifdef __cplusplus
175179
}
176180
#endif

‎mysql-test/r/lock_multi.result‎

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -348,3 +348,91 @@ commit;
348348
# Switching to connection 'default'.
349349
drop view v1;
350350
drop table t1;
351+
#
352+
# Bug#45225 Locking: hang if drop table with no timeout
353+
#
354+
# These tests also provide function coverage for the
355+
# lock_wait_timeout server variable.
356+
#
357+
DROP TABLE IF EXISTS t1;
358+
CREATE TABLE t1 (id int);
359+
SET SESSION lock_wait_timeout= 1;
360+
#
361+
# Test 1: acquire exclusive lock
362+
#
363+
# Connection default
364+
START TRANSACTION;
365+
INSERT INTO t1 VALUES (1);
366+
# Connection 2
367+
DROP TABLE t1;
368+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
369+
# Connection default
370+
COMMIT;
371+
#
372+
# Test 2: upgrade shared lock
373+
#
374+
# Connection default
375+
START TRANSACTION;
376+
SELECT * FROM t1;
377+
id
378+
1
379+
# Connection 2
380+
ALTER TABLE t1 RENAME TO t2;
381+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
382+
# Connection default
383+
COMMIT;
384+
#
385+
# Test 3: acquire shared lock
386+
#
387+
# Connection default
388+
LOCK TABLE t1 WRITE;
389+
# Connection 2
390+
INSERT INTO t1(id) VALUES (2);
391+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
392+
# Connection default
393+
UNLOCK TABLES;
394+
#
395+
# Test 4: table level locks
396+
#
397+
# Connection default
398+
LOCK TABLE t1 READ;
399+
# Connection 2
400+
INSERT INTO t1(id) VALUES(4);
401+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
402+
# Connection default
403+
UNLOCK TABLES;
404+
#
405+
# Test 5: Waiting on Table Definition Cache (TDC)
406+
#
407+
# Connection default
408+
LOCK TABLE t1 READ;
409+
# Connection con3
410+
# Sending:
411+
FLUSH TABLES;
412+
# Connection con2
413+
SELECT * FROM t1;
414+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
415+
# Connection default
416+
UNLOCK TABLES;
417+
# Connection con3
418+
# Reaping: FLUSH TABLES
419+
#
420+
# Test 6: Timeouts in I_S queries
421+
#
422+
# Connection default
423+
CREATE TABLE t2 (id INT);
424+
LOCK TABLE t2 WRITE;
425+
# Connection con3
426+
# Sending:
427+
DROP TABLE t1, t2;
428+
# Connection con2
429+
SELECT table_name, table_comment FROM information_schema.tables
430+
WHERE table_schema= 'test' AND table_name= 't1';
431+
table_name table_comment
432+
t1 Lock wait timeout exceeded; try restarting transaction
433+
# Connection default
434+
UNLOCK TABLES;
435+
# Connection con3
436+
# Reaping: DROP TABLE t1, t2
437+
# Connection default
438+
# Cleanup

‎mysql-test/r/mysqld--help-notwin.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -225,6 +225,9 @@ The following options may be given as the first argument:
225225
the week.
226226
--local-infile Enable LOAD DATA LOCAL INFILE
227227
(Defaults to on; use --skip-local-infile to disable.)
228+
--lock-wait-timeout=#
229+
Timeout in seconds to wait for a lock before returning an
230+
error.
228231
-l, --log[=name] Log connections and queries to file (deprecated option,
229232
use --general-log/--general-log-file instead).
230233
--log-bin[=name] Log update queries in binary format. Optional (but
@@ -839,6 +842,7 @@ lc-messages en_US
839842
lc-messages-dir MYSQL_SHAREDIR/
840843
lc-time-names en_US
841844
local-infile TRUE
845+
lock-wait-timeout 31536000
842846
log-bin (No default value)
843847
log-bin-index (No default value)
844848
log-bin-trust-function-creators FALSE

‎mysql-test/r/mysqld--help-win.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -224,6 +224,9 @@ The following options may be given as the first argument:
224224
the week.
225225
--local-infile Enable LOAD DATA LOCAL INFILE
226226
(Defaults to on; use --skip-local-infile to disable.)
227+
--lock-wait-timeout=#
228+
Timeout in seconds to wait for a lock before returning an
229+
error.
227230
-l, --log[=name] Log connections and queries to file (deprecated option,
228231
use --general-log/--general-log-file instead).
229232
--log-bin[=name] Log update queries in binary format. Optional (but
@@ -842,6 +845,7 @@ lc-messages en_US
842845
lc-messages-dir MYSQL_SHAREDIR/
843846
lc-time-names en_US
844847
local-infile TRUE
848+
lock-wait-timeout 31536000
845849
log-bin (No default value)
846850
log-bin-index (No default value)
847851
log-bin-trust-function-creators FALSE

‎mysql-test/suite/sys_vars/r/lock_wait_timeout_basic.result‎

Lines changed: 177 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,177 @@
1+
SET @start_global_value = @@global.lock_wait_timeout;
2+
SELECT @start_global_value;
3+
@start_global_value
4+
31536000
5+
SET @start_session_value = @@session.lock_wait_timeout;
6+
SELECT @start_session_value;
7+
@start_session_value
8+
31536000
9+
'#--------------------FN_DYNVARS_002_01-------------------------#'
10+
SET @@global.lock_wait_timeout = 100;
11+
SET @@global.lock_wait_timeout = DEFAULT;
12+
SELECT @@global.lock_wait_timeout;
13+
@@global.lock_wait_timeout
14+
31536000
15+
SET @@session.lock_wait_timeout = 200;
16+
SET @@session.lock_wait_timeout = DEFAULT;
17+
SELECT @@session.lock_wait_timeout;
18+
@@session.lock_wait_timeout
19+
31536000
20+
'#--------------------FN_DYNVARS_002_02-------------------------#'
21+
SET @@global.lock_wait_timeout = @start_global_value;
22+
SELECT @@global.lock_wait_timeout = 31536000;
23+
@@global.lock_wait_timeout = 31536000
24+
1
25+
SET @@session.lock_wait_timeout = @start_session_value;
26+
SELECT @@session.lock_wait_timeout = 31536000;
27+
@@session.lock_wait_timeout = 31536000
28+
1
29+
'#--------------------FN_DYNVARS_002_03-------------------------#'
30+
SET @@global.lock_wait_timeout = 1;
31+
SELECT @@global.lock_wait_timeout;
32+
@@global.lock_wait_timeout
33+
1
34+
SET @@global.lock_wait_timeout = 60020;
35+
SELECT @@global.lock_wait_timeout;
36+
@@global.lock_wait_timeout
37+
60020
38+
SET @@global.lock_wait_timeout = 65535;
39+
SELECT @@global.lock_wait_timeout;
40+
@@global.lock_wait_timeout
41+
65535
42+
'#--------------------FN_DYNVARS_002_04-------------------------#'
43+
SET @@session.lock_wait_timeout = 1;
44+
SELECT @@session.lock_wait_timeout;
45+
@@session.lock_wait_timeout
46+
1
47+
SET @@session.lock_wait_timeout = 50050;
48+
SELECT @@session.lock_wait_timeout;
49+
@@session.lock_wait_timeout
50+
50050
51+
SET @@session.lock_wait_timeout = 65535;
52+
SELECT @@session.lock_wait_timeout;
53+
@@session.lock_wait_timeout
54+
65535
55+
'#------------------FN_DYNVARS_002_05-----------------------#'
56+
SET @@global.lock_wait_timeout = 0;
57+
Warnings:
58+
Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
59+
SELECT @@global.lock_wait_timeout;
60+
@@global.lock_wait_timeout
61+
1
62+
SET @@global.lock_wait_timeout = -1024;
63+
Warnings:
64+
Warning 1292 Truncated incorrect lock_wait_timeout value: '-1024'
65+
SELECT @@global.lock_wait_timeout;
66+
@@global.lock_wait_timeout
67+
1
68+
SET @@global.lock_wait_timeout = 31536001;
69+
Warnings:
70+
Warning 1292 Truncated incorrect lock_wait_timeout value: '31536001'
71+
SELECT @@global.lock_wait_timeout;
72+
@@global.lock_wait_timeout
73+
31536000
74+
SET @@global.lock_wait_timeout = ON;
75+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
76+
SELECT @@global.lock_wait_timeout;
77+
@@global.lock_wait_timeout
78+
31536000
79+
SET @@global.lock_wait_timeout = OFF;
80+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
81+
SELECT @@global.lock_wait_timeout;
82+
@@global.lock_wait_timeout
83+
31536000
84+
SET @@global.lock_wait_timeout = test;
85+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
86+
SELECT @@global.lock_wait_timeout;
87+
@@global.lock_wait_timeout
88+
31536000
89+
SET @@session.lock_wait_timeout = 0;
90+
Warnings:
91+
Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
92+
SELECT @@session.lock_wait_timeout;
93+
@@session.lock_wait_timeout
94+
1
95+
SET @@session.lock_wait_timeout = -2;
96+
Warnings:
97+
Warning 1292 Truncated incorrect lock_wait_timeout value: '-2'
98+
SELECT @@session.lock_wait_timeout;
99+
@@session.lock_wait_timeout
100+
1
101+
SET @@session.lock_wait_timeout = 31537000;
102+
Warnings:
103+
Warning 1292 Truncated incorrect lock_wait_timeout value: '31537000'
104+
SELECT @@session.lock_wait_timeout;
105+
@@session.lock_wait_timeout
106+
31536000
107+
SET @@session.lock_wait_timeout = ON;
108+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
109+
SELECT @@session.lock_wait_timeout;
110+
@@session.lock_wait_timeout
111+
31536000
112+
SET @@session.lock_wait_timeout = OFF;
113+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
114+
SELECT @@session.lock_wait_timeout;
115+
@@session.lock_wait_timeout
116+
31536000
117+
SET @@session.lock_wait_timeout = test;
118+
ERROR 42000: Incorrect argument type to variable 'lock_wait_timeout'
119+
SELECT @@session.lock_wait_timeout;
120+
@@session.lock_wait_timeout
121+
31536000
122+
'#------------------FN_DYNVARS_002_06-----------------------#'
123+
SELECT @@global.lock_wait_timeout = VARIABLE_VALUE
124+
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
125+
WHERE VARIABLE_NAME='lock_wait_timeout';
126+
@@global.lock_wait_timeout = VARIABLE_VALUE
127+
1
128+
'#------------------FN_DYNVARS_002_07-----------------------#'
129+
SELECT @@session.lock_wait_timeout = VARIABLE_VALUE
130+
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
131+
WHERE VARIABLE_NAME='lock_wait_timeout';
132+
@@session.lock_wait_timeout = VARIABLE_VALUE
133+
1
134+
'#------------------FN_DYNVARS_002_08-----------------------#'
135+
SET @@global.lock_wait_timeout = TRUE;
136+
SELECT @@global.lock_wait_timeout;
137+
@@global.lock_wait_timeout
138+
1
139+
SET @@global.lock_wait_timeout = FALSE;
140+
Warnings:
141+
Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
142+
SELECT @@global.lock_wait_timeout;
143+
@@global.lock_wait_timeout
144+
1
145+
'#---------------------FN_DYNVARS_001_09----------------------#'
146+
SET @@global.lock_wait_timeout = 10;
147+
SET @@session.lock_wait_timeout = 11;
148+
SELECT @@lock_wait_timeout = @@global.lock_wait_timeout;
149+
@@lock_wait_timeout = @@global.lock_wait_timeout
150+
0
151+
'#---------------------FN_DYNVARS_001_10----------------------#'
152+
SET @@lock_wait_timeout = 100;
153+
SELECT @@lock_wait_timeout = @@local.lock_wait_timeout;
154+
@@lock_wait_timeout = @@local.lock_wait_timeout
155+
1
156+
SELECT @@local.lock_wait_timeout = @@session.lock_wait_timeout;
157+
@@local.lock_wait_timeout = @@session.lock_wait_timeout
158+
1
159+
'#---------------------FN_DYNVARS_001_11----------------------#'
160+
SET lock_wait_timeout = 1;
161+
SELECT @@lock_wait_timeout;
162+
@@lock_wait_timeout
163+
1
164+
SELECT local.lock_wait_timeout;
165+
ERROR 42S02: Unknown table 'local' in field list
166+
SELECT session.lock_wait_timeout;
167+
ERROR 42S02: Unknown table 'session' in field list
168+
SELECT lock_wait_timeout = @@session.lock_wait_timeout;
169+
ERROR 42S22: Unknown column 'lock_wait_timeout' in 'field list'
170+
SET @@global.lock_wait_timeout = @start_global_value;
171+
SELECT @@global.lock_wait_timeout;
172+
@@global.lock_wait_timeout
173+
31536000
174+
SET @@session.lock_wait_timeout = @start_session_value;
175+
SELECT @@session.lock_wait_timeout;
176+
@@session.lock_wait_timeout
177+
31536000

0 commit comments

Comments
 (0)