SECURITY - LOCK CONNECTION <id>, UNLOCK CONNECTION SQL command

Sometimes a program start a query batch with many many queries and we want to "pause" the program, we have (5) problems to solve in this MDEV
but the program don't have a "pause" button, and we as dba must lock it to allow others users to use database and after some time unlock it and continue program batch, well that's the first (1) problem of this MDEV
The same happen for shared hosts where many applications execute queries with high i/o or high cpu use, and we need to throttle / pause this queries, here is just to pause, throttle is another feature but it will not be included here

well the MDEV here is a LOCK / UNLOCK CONNECTION, SQL command.
Ideas:

LOCK CONNECTION <connection id> [TIMEOUT <timeout>] [WITHOUT/WITH DEADLOCK] [WITHOUT/WITH QUERY CACHE] [PAUSE QUERY]

      must have: <connection id>

      optional: [TIMEOUT timeout] [WITHOUT/WITH DEADLOCK] [WITHOUT/WITH QUERY CACHE] [PAUSE QUERY]

      here default: 

            timeout=0 (forever)  

            WITH DEADLOCK (if connection that owned the LOCK CONNECTION disconect, all connections locked won't be unlocked)

            WITH QUERY CACHE (if query is in query cache return, just block queries that aren't in query cache, deletes, handler, update, alter, etc...)

            without PAUSE QUERY - in the end of this MDEV i explain what it do, it's near KILL but don't drop connection

UNLOCK CONNECTION <connection id>

      must have: <connection id>

the second (2) problem is deadlock, if we LOCK CONNECTION and we disconnect from server (internet connections are very bad with cellphones and <3g bands) the connection will unlock, well that's why i put the "WITHOUT DEAD LOCK" in LOCK CONNECTION

now the third (3) and last one problem, is know if a connection is or isn't locked, for this we should show this information in PROCESS LIST
a new column with "CONNECT_LOCKED", ENUM ('Y','N') can solve the problem (maybe the connection id that locked this query)

well that's all guys
if anyone could help me how to add "LOCK CONNECTION" words in parser i could do/help with this patch without big problems

resuming.. with this we will have 4 commands to admin connections:

1)SHOW PROCESSLIST; (problem 3)

2)KILL [USER | QUERY] xxxx;  (done)

3)LOCK CONNECTION xxx [TIMEOUT yyy]; (problem 1)

3)LOCK CONNECTION xxx [TIMEOUT yyy] WITHOUT DEADLOCK; (problem 2)

4)UNLOCK CONNECTION (problems (1) and (2) )

i think it will help many DBAs (including me =) )

---- implementation

"LOCK CONNECTION <id>"
1)search the process (id),
2)lock it

    i think it's a THD variable, and a mutex lock it to prevent THD being changed or deleted from memory

3) write that the connection is locked

      a new variable "lock_connection_from" in THD

      0 = unlocked

      -1 = locked with a connection that don't exists (deadlock)

      >0 = locked with a connection id that exists

4) write the flags

      lock_connection_timeout in seconds

      lock_connection_lock_time the time we set the lock (for timeout)

      lock_connection_with_query_cache  (boolean, 0=without query cache, 1 = with query cache [default])

      lock_connection_with_deadlock (boolean, 0=without deadlock [default], 1 = with deadlock)

5) unlock THD
6) return
-------------
UNLOCK:

1)lock THD

2) set lock_connection_from=0

3)unlock THD

-------------
at sql_parse (if i'm not wrong, that's where query_cache_send_result() is called) :

0) wait packages <-- if connection was locked here don't lock until (2)
1) receive sql <-- if connection was locked here don't lock until (2)
2) wait until connection is locked <--- here check if it's locked or not

wait() = sleep(0,001 second)

"check log" procedure:

      if lock_connection_from=0, { unlocked go to (3) }

      if (now() - lock_connection_lock_time >lock_connection_timeout AND lock_connection_timeout>0), { set lock_connection_from=0, timedout and unlocked, go to (3) }

      if (query in query cache and lock_connection_with_query_cache =1), {"in query cache", go to (3) }

      if lock_connection_from=-1, { wait() and go back to "check lock" again }

      if lock_connection_from>0 {

          check if connection exists in processlist (i don't know how to do this in source today, but i know it can be done)

          if exist{ wait() and go back to "check lock" again }

          if not, {

                    check flag lock_connection_with_deadlock

                    if lock_connection_with_deadlock=0,{ set lock_connection_from=0, go to (3)}

                    if lock_connection_with_deadlock=1,{ set lock_connection_from=-1, wait() and go back to "check lock" again }

          }

      }

      go back to "check log"

(3)

 |

 v

3) parse <-- if the connection was locked here, continue with parse, optimize, execute, return
4) optimize <-- if the connection was locked here, continue with optimize, execute, return
5) execute <-- if the connection was locked here, continue with execute, return
6) return <-- if the connection was locked here, continue with return and go back to (0)

it will work like a query waiting a table with "LOCK TABLE xxx" but no table will be locked
query will wait connection lock be removed before continue to parse the query (or check query cache)

------------
at PROCESS_LIST
add "CONNECTION LOCKED" with "lock id + lock timeout + lock type (dead lock and query cache)", example:

unlocked:

"UNLOCKED" or NULL or "" or "123456 TIMEDOUT"  (when processlist can check that it's timedout) or 

"DEADLOCKED TIMEDOUT"  (when processlist can check that it's timedout)

locked:

"123456 TIMEOUT 1234 WITH DEADLOCK WITH QUERY CACHE, TIMEOUT IN xxx SECONDS"

"123456 WITH DEADLOCK WITH QUERY CACHE" (without timeout)

locked but deadlocked (the locker connection was killed and lock persist)

"DEADLOCKED TIMEOUT 1234 WITH QUERY CACHE"

"DEADLOCKED WITH QUERY CACHE"

"DEADLOCKED"

i think a string is easier to understand, since it's only for DBAs
we don't need how many time it was locked, since we can get it at TIME_MS column

----------------------
PROBLEM (4) AND (5)
PAUSE QUERY option

maybe we could do 'online LOCK', in this case i will call it like a 'PAUSE' query, since we will not drop connection, just stop the execution and we unlock it we will restart or continue the job

instead of wait query do the job, and after lock connection we will stop query and lock connection

in this case:
when lock (LOCK CONNECTION <id> PAUSE QUERY) (problem (4)):
remove locks, put thread in a 'pause' status and save all 'done' job (selected rows, query plan, etc)
when unlock (UNLOCK CONNECTION <id>) (problem (5)):
put thread in a 'resuming' status, and
if tables changed => clean 'done' job and restart select
if tables don't changed => restore 'done' job and continue select where it was

for UPDATE/DELETE/ALTER i think we can't do this since some engines are not transactional or some commands are not 'ONLINE', but for transactional engines maybe we could do something like:

BEGIN TRANSACTION
selects
updates
inserts
....

LOCKED CONNECTION HERE
save all SQL commands in this transaction (selects, updates, inserts)
execute a "ROLLBACK"

....
UNLOCKED HERE
retrieve all SQL commands in the last transaction, execute all commands again and continue transaction