What is the recommended way to determine which connections are blocked by semaphores? The ReqStatus shows up as Executing and BlockedOn as zero...

Some candidates...

LockObjectOID and LockObjectType connection properties.

sa_conn_info.LockObject and LockObjectType which are (?) pretty much the same thing.

sp_list_mutexes_semaphores.currently_waited_for, which appears to contain at least one victim's connection id.

...not sa_locks(), however, that only lists mutexes.

Are the currently_owned_by and currently_waited_for columns returned by sp_list_mutexes_semaphores() going to be changed to return Y/N/null like DCX says, or are they going to continue return a connection number like they do now?

What about mutexes and semaphores that block multiple victims? (that makes Y/N seem the likely choice, which in turn makes DCX more like specs and less like docs :)

asked 17 Nov '15, 10:56

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 24 Nov '15, 08:11

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

1

Ah, I hear a Top 17 New Features coming... - and if so, I'd be really glad to learn about those "inner" details, not the Cockpit UI stuff... (I'm usually glad with dbsrvX -o console.log).

(17 Nov '15, 11:07) Volker Barth
Replies hidden

> a Top 17 New Features coming

Possibly, but the reason for this question is that Foxhound must now deal with mutexes and semaphores when answering the question, "Who's blocking who, and how?"

(17 Nov '15, 15:24) Breck Carter

sa_conn_info() and sp_list_mutexes_semaphores() result sets include this information.

sa_conn_info():

The following fields can be used to distinguish blocking on semaphore from blocking on a mutex or a table:

  • BlockedOn - the value is 0, as the connection waiting on a semaphore is not blocked by another connection.
  • LockTable - the value is '', as the lock object is not a table.
  • LockObject - the name of the semaphore the connection is blocked on.
  • LockObjectType - 'MUTEX SEMAPHORE'

sp_list_mutexes_semaphores:

  • currently_waited_for_field - a comma separated list of connection numbers for connections waiting on the mutex/semaphore
  • currently_owned_by - a comma separated list (in case of locking in shared mode) of connections currently holding a mutex

Documentation for the currently_waited_for field will need to be fixed to reflect this information.

permanent link

answered 17 Nov '15, 14:25

Elmi%20Eflov's gravatar image

Elmi Eflov
8061114
accept rate: 36%

edited 17 Nov '15, 14:47

Please confirm that as far as sa_conn_info() is concerned, BlockedOn is the only way to tell a mutex apart from a sempaphore: zero for a semaphore block, non-zero for a mutex block.

(17 Nov '15, 15:21) Breck Carter
Replies hidden

That is correct. The main difference between those two types of user synchronization objects that, when a connection is blocked on a semaphore, there is no other connection it is explicitly waiting on.

(17 Nov '15, 17:56) Elmi Eflov

Just to clarify: Say, I have a connection-scope mutex which is owned by two connections with numbers 6 and 7 in share mode (so sp_list_mutexes_semaphores() list both in "currently_owned_by"). Say, another connection (no. 5) wants to lock that mutex in exclusive mode and therefore is blocked, so that connection is listed as "currently_waited_for".

Here, sa_conn_info() only lists one of the connections holding the shared lock in "BlockedOn" (here with value 6) for the blocked connection no. 5. Is that limitation by design? (IMHO that connection is blocked by two others, not by one. - In the sample, once conn. 6 releases the lock but conn. 7 still holds it, then "BlockedOn" changes to value 7.)

(23 Nov '15, 15:27) Volker Barth
1

The behavior is by design and is consistent with row/table locking.

In your example, replace the mutex with a row in a table, shared mode with read lock, and exclusive mode with write lock - the behavior will be the same, namely, only one connection will be listed as the blocking connection.

(23 Nov '15, 16:28) Elmi Eflov
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7
×4

question asked: 17 Nov '15, 10:56

question was seen: 1,862 times

last updated: 24 Nov '15, 08:11