Hi, As per my understanding on the non durable subscription flow in MB, when adding a subscription for a topic (ex:topic1), it will create an internal role if topic1 is not exists. After adding an internal role, it'll add permissions to the role such as publish, subscribe etc. This will update the UM_PERMISSION and UM_ROLE_PERMISSION tables. In UM_PERMISSION table it will add a resourceId per a topic. And the role created for a topic will be deleted with the permissions available to that, when all the subscriptions for the same topic are disconnected. So that the row level changes in UM_PERMISSION will occur when adding and deleting a topic.
When adding a topic, an INSERT statement in UM_PERMISSION table, will place an exclusive lock on rows until that has been committed (or rolled back). In there, a SELECT might be blocked, since in the mean time this subscription in another node, will be accessing permissions for the same topic in UM_PERMISSION table, by considering that the permissions are available since the role is created for the topic. But the deadlock occurs in "addAuthorizationForRole" method in JDBCAuthorizationManager class, where it adds permissions for the role. Two nodes can't be adding and giving permissions to the same topic, so that this can't be a row level locking and has to be a table level locking. When deleting a topic, another subscription for same topic can't be exists, so in that scenario also a row level locking can't be occur due to SELECT, but a table level locking can be exists. This may be the reason that this works for oracle but not for mssql and can be because of the Lock Escalation available in SQL Server or as in the [1] some versions of SQL Server doesn't support row level locking. Please refer [1] for the difference in locking mechanism in Oracle and SQL Server . If the reason behind this is lock escalation, that can be avoided by introducing proper indexing mechanism. And "WITH (NOLOCK)" or "WITH (READPAST)" also can be useful in mssql to avoid deadlocks, but it'll lead to invalid results or dirty reads. So as tharindu mentioned, the best way to avoid this can be removing changes done in UM_PERMISSION table. [1] https://docs.oracle.com/cd/E10405_01/appdev.120/e10379/ss_oracle_compared.htm#i1038519 Thanks and Regards -- Indunil Upeksha Rathnayake Software Engineer | WSO2 Inc Email [email protected]
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
