web stats
Large number of Database Deadlock errors using Channel Writter - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 09-03-2010, 08:41 AM
TSmithTRA TSmithTRA is offline
OBX.2 Kenobi
 
Join Date: Nov 2009
Location: Winston-Salem, NC
Posts: 79
TSmithTRA is on a distinguished road
Default Large number of Database Deadlock errors using Channel Writter

I am experiencing a high volume of Database deadlock errors on the the MIRTH Database.

I am using SQL Server 2005 32bit on a Windows Server 2003R2 server.

The server has 8 cores with 8GB of RAM.

I traced the messages back to a particular channel that had a high message volume.

The channel had a channel writer as the destination.

When I changed it to a file writer and modified the receiving channel as a file reader the errors went away.

Does anyone have any ideas or suggestions.


ERROR 2010-09-03 10:38:56,714 [b85b33f6-5801-46a0-9786-3c173d5a5485_destination_1_connector.028f237d-b1d9-4ebb-a1b3-47485ad748f2.receiver.1] com.webreach.mirth.server.controllers.DefaultMessa geObjectController: Error updating message dc6cfe2a-d9c2-499c-aff6-60ab76f8ba90 status due to a database problem
com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
--- The error occurred in sqlserver2005/sqlserver2005-message.xml.
--- The error occurred while applying a parameter map.
--- Check the Message.updateMessageStatus-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Transaction (Process ID 165) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Caused by: java.sql.SQLException: Transaction (Process ID 165) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.update(SqlMapExecutorDelegate.java:505)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.up date(SqlMapSessionImpl.java:90)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.upd ate(SqlMapClientImpl.java:67)
at com.webreach.mirth.server.controllers.DefaultMessa geObjectController.updateMessageStatus(DefaultMess ageObjectController.java:240)
__________________
Tom Smith
IT Director
Triad Radiology Associates
Reply With Quote
  #2  
Old 09-15-2010, 06:42 AM
beefmon beefmon is offline
OBX.2 Kenobi
 
Join Date: Oct 2009
Posts: 62
beefmon is on a distinguished road
Default

I am seeing the same on a few of my LLP destination channels that I have enabled persistent queues.

I see the errors but when I look under the covers, it looks like the message gets sent anyway.

My $0.02,

beefmon
Reply With Quote
  #3  
Old 09-15-2010, 08:03 AM
TSmithTRA TSmithTRA is offline
OBX.2 Kenobi
 
Join Date: Nov 2009
Location: Winston-Salem, NC
Posts: 79
TSmithTRA is on a distinguished road
Default

This did become more of an issue once I enabled persistent Queues. It does appear that the message is sent. My concern is more how this will affect overall performance of the system.
__________________
Tom Smith
IT Director
Triad Radiology Associates
Reply With Quote
  #4  
Old 12-23-2010, 08:39 AM
fransdw fransdw is offline
OBX.1 Kenobi
 
Join Date: Feb 2007
Location: Tallahassee, FL
Posts: 43
fransdw is an unknown quantity at this point
Send a message via Skype™ to fransdw
Talking Resolution

I have found a resolution to this issue for myself using SQL Server 2005. None of the SELECT, INSERT, UPDATE, or DELETE statements uses any hinting and as such the database server stupidly chose to lock pages instead of rows.

I have updated the statements and added hinting (attached). If someone sees any problems with this please let me know. So far our high volume server is handling the message volume beautifully without any lock contention issues. Wooha.

This file is placed in <mirth-home>/conf/sqlserver2005/

Thanks,
Frans
Attached Files
File Type: xml sqlserver2005-message.xml (12.7 KB, 67 views)
Reply With Quote
  #5  
Old 12-28-2010, 06:58 AM
fransdw fransdw is offline
OBX.1 Kenobi
 
Join Date: Feb 2007
Location: Tallahassee, FL
Posts: 43
fransdw is an unknown quantity at this point
Send a message via Skype™ to fransdw
Talking Further performance improvement

Our DBA's found out that the parameters to the insert/update/delete etc queries (especially on the message table) were coming across as nvarchar. The message table, ID column is set as varchar. The conversion and subsequent mismatch with index etc caused severe performance degrading.

They changed the type of the ID column in the MESSAGE table from varchar to nvarchar.

We experienced a 10 fold increase in throughput and performance. If not more ... ;-)

Woooha!

Frans
Reply With Quote
  #6  
Old 05-02-2012, 06:26 AM
pazaq pazaq is offline
Mirth Newb
 
Join Date: Nov 2011
Posts: 21
pazaq is on a distinguished road
Default Is still necessary?

I noticed the ID still says varchar in my database. Is this really affect my performance? Anybody have any idea? I can't find an error attached to it.
Reply With Quote
Reply

Tags
channel writer, database deadlock

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 09:42 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Mirth Corporation