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
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?
answered 23 Nov '15, 16:18
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.
answered 23 Nov '15, 16:23