Query to show blocking information

Help
1

Hi ,

How can we check who is blocking who through memsql query?

Thanks,
Asrar

2

Hi Asrar

I am not quite sure I understand your question – what do you mean by who is blocking who?

Thanks!

3

Hi Jacky,

Just need the information related to query blocking.

Which query is getting blocked currently in the concurrent queries,
Which query is the culprit to cause blocking ,

Thanks!

Asrar

4

To see the state of queries (i.e. executing/queued etc), run

select * from information_schema.processlist;

The STATE column will specify the state of the queries.

E.g.

memsql> select id, user, command, state, info from information_schema.processlist;
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+
| id  | user        | command | state     | info                                                                      |
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+
| 134 | root        | Query   | queued    | select "This query gets queued", sleep(1000000)                           |
| 110 | root        | Query   | executing | select "This query runs", sleep(1000000)                                  |
|  23 | root        | Query   | executing | select id, user, command, state, info from information_schema.processlist |
|  21 | distributed | Sleep   |           | NULL                                                                      |
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+

To understand which query is causing other queries to get queued, can you send the output of following queries (when your system is in a state where queries are queued):
SHOW WORKLOAD MANAGEMENT STATUS;
select pc.plan_id, pc.optimizer_notes, pl.info from information_schema.plancache as pc join information_schema.processlist as pl on pl.plan_id = pc.plan_id;

Are you using resource pools? (I am assuming you are not).

5

I realize this is an older 2019 thread, but the question still stands. its genuine.

memSQL does not provide “who is blocking whom” as say what other DBMSs provide e.g. sp_who of sqlserver or SAP/ASE. its very valuable for DBAs to track the lead offender in a blocking nightmare…

and we are not talking about workload mgmt. and resource-pools used here. my vote is : augment the “processlist” to add the blocker column. case closed.

6

MemSQL 7.1 will have some improvements here.

  • A new info schema view that shows queries blocking on locks (and who is holding those locks)
  • rowlock timeout errors will show which query was holding the rowlock or metadata lock
  • processlist will show which queries are holding a row lock or metadata lock that is blocking a query from making progress
7

And MemSQL 7.1 ships at the end of April 2020. Stay tuned.

8

Outstanding! a very desirable feature given doing production support when ‘all hell breaks loose’ with locking nightmares can now simply be resolved. I am also hoping memsql studio and historical monitoring can record some tell-tale signs of locking entanglement (just so that appdev can plan a better data model)( I equate this what 23andme would do to tell me my DNA blueprint and subsequent mutation would cause me to have some problem down the road). you guys are fantastic!