SET STATEMENT affects the value of the GLOBAL variable for the duration of the statement

# Initial values

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

@@global.lock_wait_timeout	@@lock_wait_timeout

31536000	31536000

# 

set statement lock_wait_timeout=11 for show global variables like 'lock_wait_timeout';

Variable_name	Value

lock_wait_timeout	31536000

SET STATEMENT lock_wait_timeout=12 FOR SELECT SLEEP(2.5);

connect con1,localhost,root;

# 

# It's a new connection, so it picked up the current value 

# of the global variable for the session variable

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

@@global.lock_wait_timeout	@@lock_wait_timeout

12	12

# 

# Now SET STATEMENT in the other connection ended, so the global value

# is back to normal (but the session one is not)

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

@@global.lock_wait_timeout	@@lock_wait_timeout

31536000	12

--enable_connect_log

--echo # Initial values

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

--echo # 

set statement lock_wait_timeout=11 for show global variables like 'lock_wait_timeout';

--send SET STATEMENT lock_wait_timeout=12 FOR SELECT SLEEP(2.5)

--sleep 1

--connect(con1,localhost,root)

--echo # 

--echo # It's a new connection, so it picked up the current value 

--echo # of the global variable for the session variable

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

--sleep 2

--echo # 

--echo # Now SET STATEMENT in the other connection ended, so the global value

--echo # is back to normal (but the session one is not)

SELECT @@global.lock_wait_timeout, @@lock_wait_timeout;

commit 3a3f88e789a41b309cfe902f4823c9c7ebbce6ca

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Fri Oct 24 10:13:08 2014 +0200

    MDEV-5231: Per query variables from Percona Server (rewritten)