Scope Clause in CREATE MUTEX

[Baron]

According to the document the clause SCOPE can be eiter TRANSACTION or CONNECTION (default).

If I have two connections (A&B) into my SQL 17 Database, and if I execute the following statement in connection A:

create mutex mymutex;

then mymutex is also visibile in Connection B, moreover I can also lock, release, drop the mymutex from Connection B.

Could somebody please explain what is exactly meant with SCOPE = Connection?

[VolkerBarth · Accepted solution]

If you could not use a mutex from more than one single connection, what would be the use of a mutex as a locking/synchronization facility? 🙂

To the scope, see the docs, to quote:

The scope of a mutex can be either transaction or connection. In transaction-scope mutexes, the lock is held until the end of the transaction that has locked the mutex. In connection-scope mutexes, the lock is held until a RELEASE MUTEX statement is executed by the connection or until the connection terminates.

In other words: While locked transaction-scoped mutexes are automatically unlocked when a transaction ends (and cannot be explicitely unlocked), connection-scoped mutexes can be locked and unlocked independent of transaction boundaries.

[Baron · Accepted solution]

@volker Barth One more question: What I am going to get is the following: I have an exe file which connects to my database and I want to prevent simultanous connections from this same exe to my database.

So in the begining of connection i check whether the mutex is free select * from sp_list_mutexes_semaphores() where "name" = 'mymutex' and then I lock it LOCK MUTEX mymutex otherwise I close the connection and terminate the exe.

Do you think that in this way I still have any risk for racing problem?

[VolkerBarth · Accepted solution]

You could also use a LOGIN_PROCEDURE to check via the application's name whether it should be allowed to connect or not - but I guess the mutex approach seems fine here. (I have never thought about that use case...)

Of course I cannot tell whether the actions done by that application are free from race conditions... If it does something "critical", further mutexes/semaphores might be helpful to allow or limit the access for other applications.

Here mutexes are possible more powerful and controllable than other kinds of locks as they can be hold beyond transactions and checkpoints, say, until you have done some critical change involving DDL statements or a bunch of transactions, ... It's just up to you...

[Baron · Accepted solution]

I am worrying about this scenario: 2 instances of the exe (possibly from different machines) start simultaneously so the database gets concurrent 2 connections, so both connections get a signal that the mutex is free (released) if the query select * from sp_list_mutexes_semaphores() where "name" = 'mymutex' is executed really simultaneously from two differenct connections (database is running on a multi-thread machine)

The next statement in each connection is lock mutex mymutex and this guarantees that one of the exes will continue in the DB, but this will cause the relative belated connection to wait until the first connection is finish, and this is exactly what I need to avoid (running the exe in zombi).

[former_SQLA_member1694875 · Accepted solution]

this will cause the relative belated connection to wait until the first connection is finish

The LOCK MUTEX statement has an optional TIMEOUT clause, which can help prevent this.

[Baron · Accepted solution]

@Arthoor Thanks for the help, it works and it brings SQLCODE=-1231, ODBC 3 State ="HY000".

My next question is, how can I handle this error in an sql script?

I tried select sqlstate immediately after lock mutex mymutex timeout 1000 but I get the error and can't handle it.

[VolkerBarth · Accepted solution]

I'd write a small wrapper function that (optionally checks whether the mutex is locked and otherwise) tries to lock it with the specified timeout, and use the usual exception handling within functions/code blocks to handle that error. Just a true/false state whether the mutex could be locked.

Of course you can do this without a function/procedure by simply using a BEGIN...END block with an exception handler.

[Baron · Accepted solution]

@volker Barth, thanks, I was looking for the syntax of Exception handling, now I found it.

In this case I don't even need to manually check the status of the mutex using select * from sp_list_mutexes_semaphores() where "name" = 'mymutex'

[Baron]

Thank you all. Here is a snippet how I implemented the function ConnectAlone, and how I simulated simultaneous calls over internal events. It wokrs perfect as only one event succeed to get the connection.

The problem when I call the function ConnectAlone from an exe, then I get sometimes unexpected connections, so that both exes get connected 'alone' in the database.

But I am still looking for the reason, it can be related to the exe itself.

create or replace mutex MyMutex;
create or replace function ConnectAlone() 
returns bit 
begin 
if ((select currently_owned_by from sp_list_mutexes_semaphores() where "name" = 'MyMutex') is null) then 
    begin 
        lock mutex MyMutex timeout 1000; 
        return 1; 
        exception when others then 
            return 0; 
    end; 
else 
    return 0; 
end if; 
end;

create or replace event myevent1 schedule start time '00:00' every 2 minutes enable handler 
BEGIN 
if (ConnectAlone() = 1) then 
    message (now() || ' Myevent1 Succeed'); 
    waitfor delay '00:00:10'; 
    release mutex MyMutex; 
else 
    message (now() || ' Myevent1 failed'); 
end if; 
end;

create or replace event myevent2 schedule start time '00:00' every 2 minutes enable handler 
BEGIN 
if (ConnectAlone() = 1) then 
    message (now() || ' Myevent2 Succeed'); 
    waitfor delay '00:00:10'; 
    release mutex MyMutex; 
else 
    message (now() || ' Myevent2 failed'); 
    end if; 
end;

[VolkerBarth]

Please enclose code in a "pre"-tag pair, not as quoted text, it would be easier to read...

[VolkerBarth]

so that both exes get connected 'alone'

Well, I'm quite sure that two connections cannot lock the same mutex (at least in the default "EXCLUSIVE" mode), otherwise it wouldn't make sense as a useful IPC mechanism...

I would also wrap the event handler code with exception handling, as a RELEASE MUTEX may also fail.

How is the mutex created (and potentially dropped), is this a permanent one?