Since a connection can lock the same mutex several times (i.e. recursively) without further blocking, is there a way to find out "how often" the LOCK MUTEX statement has been used for that according connection and mutex?

Or do I have to call RELEASE MUTEX on the lock until it issues a SQLCODE -1813 error (Mutex "MX_Test" was not locked by this connection)?

asked 23 Nov '15, 15:51

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

Just to add: AFAIK, mutexes in the Win32 API behave similar, and there is no access to the "internal counter", i.e. there you have to count the number of calls by yourself or to call ReleaseMutex() until it fails.

(23 Nov '15, 15:59) Volker Barth

I don't believe you can see the internal count, but you can determine which connection is currently holding the mutex with sp_list_mutexes_semaphores(). You can also use sa_locks() to determine whether your connection holds the mutex. Is this good enough? Or do you need to know the actual count?

permanent link

answered 23 Nov '15, 16:18

Phil%20Mitchell's gravatar image

Phil Mitchell
1.9k1831
accept rate: 27%

Yes, that's good enough - so I could choose between

  • calling RELEASE MUTEX in a loop until it fails with -1813

  • or check with sp_list_mutexes_semaphores() or sa_locks() after each RELEASE MUTEX call to see it the mutex has been fully released

  • or do the internal counting myself or make otherwise clear that both calls are always paired.

Enough choices, methinks:)

(24 Nov '15, 07:40) Volker Barth

There is no way to know how many times the mutex was locked by the current connection.

If the mutex is a connection scope mutex, the only way to fully release it is to call the RELEASE statement as many times as you have called the LOCK statement. The expectation is that mutex acquisition and release calls are paired in the application.

For a transaction scope mutex, all lock counts will be released at COMMIT or ROLLBACK.

permanent link

answered 23 Nov '15, 16:23

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

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:

×4

question asked: 23 Nov '15, 15:51

question was seen: 275 times

last updated: 24 Nov '15, 07:40