OMW message table with blocking locks on console selects (155 Views)
Reply
Super Advisor
Graham Van der vaart_1
Posts: 556
Registered: ‎09-25-2003
Message 1 of 4 (155 Views)

OMW message table with blocking locks on console selects

Hi,

I occasionally find that the OMW OV_Message table locks up. When I issue sp_who2 on the database I see blocking locks caused by console sessions running selects against the database. These blocking locks can sometimes be seen for longer than two hours. When I observe that the console is no longer updating I may need to kill the offending session.

 

I cannot rely on my manually handling this kind of scenario. I need either a way of automatically killing sessions that block lock the message table or at the very least a SQL job that can email me if such a lock exists.

 

Anybody done anything similar and have developed some similar solution?

 

This is a mission critical implementation with a high message volume and I must have a workaround.

 

Kind Regards

Graham

Please use plain text.
Honored Contributor
Ram_21
Posts: 3,619
Registered: ‎04-30-2003
Message 2 of 4 (142 Views)

Re: OMW message table with blocking locks on console selects

Hello

 

Is the OMW server on the latest patch OMW_00178?

 

Also you mentioned that message volume is high in the environment - is database maintenance configured to handle this by purging acknowledged messsages sooner. This may help as well.

 

Hope this helps and you get a better answer.
Regards

Ram

 

Please use plain text.
Super Advisor
Graham Van der vaart_1
Posts: 556
Registered: ‎09-25-2003
Message 3 of 4 (131 Views)

Re: OMW message table with blocking locks on console selects

Hi Ram,

 

I have nearly 10 million ack messages but I am not permitted to reduce them. For this reason I need to be able to check for blocking locks or some other means of handling messages. I guess there may be some or other remote console setting that prevents locks at some or other expense (such as data integrity of console views).

 

Kind Regards

Graham

Please use plain text.
HP Expert
BeatrizR
Posts: 119
Registered: ‎06-01-2012
Message 4 of 4 (119 Views)

Re: OMW message table with blocking locks on console selects

Hello Graham,

 

The problem you are seeing could be due to a huge amount of messages that should be processed. Depending on the number of messages which qualify SQL Server might escalate the lock level to table lock. Then the table ov_ms_message is completely locked, no insert/update operation can be performed on the table.

 

I have seen this problem when DBMaint is running in order to delete acknowledged messages. For avoiding it, you could set the configuration item "Transaction isolation level during daily database maintenance" to the value 0  (read uncommitted):

 

  • Configure > Server > Namespace "Database Maintenance". Select Expert mode. Set the configuration item "Transaction isolation level during daily database maintenance" to 0.

 

Apart from that, please follow the steps for Database Maintenance in order to reduce WMI load. I will send here a document that might be useful:

 

http://support.openview.hp.com/selfsolve/document/KM00515953

 

 

Please also check the following topics in the Online Help:

 

  • Maintain acknowledged messages in the database
  • Export acknowledged messages from the database

 

 

I hope this helps.

 

Best regards,

 

Beatriz

HP Support
If you find this or any post resolves your issue, please make sure to mark it as an "Accepted as Solution".
If you liked the reply then please show this with KUDOs
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation