Deadlocks

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Locked
mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Deadlocks

Post by mhagen » 2009-10-27 17:31

Hi Martin,

I keep getting these kinds of errors:

Code: Select all

"ERROR"	2928	"2009-10-27 16:21:41.970"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: Source: ADOConnection::Execute(), Code: HM10044, Description: Error while executing SQL statement: update hm_messages set messagelocked = 1 where messageid = 5676345 Microsoft OLE DB Provider for SQL Server Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Code: Select all

"ERROR"	1016	"2009-10-27 16:23:05.046"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: Source: ADOConnection::Execute(), Code: HM10044, Description: Error while executing SQL statement: UPDATE hm_messages SET messageaccountid=469, messagefilename=N'E:\E-Mail\Data\domain.com\person\95\{95238AF3-8EDF-4D62-A9EF-60A78F789C02}.eml', messagetype=2, messagefrom=N'info@example.com', messagesize=501963, messageflags=96, messagefolderid=2386, messageuid=5517670 WHERE messageid = 5676351 Microsoft OLE DB Provider for SQL Server Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I'm running hMailserver on two Windows 2003 Servers, one with hMailserver and the other with SQL Server 2000. Can you (or anyone else in the forums) help me debug this problem?

Another question regarding the deadlocks: What happens to the messages in case of a failed DB write, does the message still get delivered?

Thanks,
Mingo.

User avatar
sheffters
Senior user
Senior user
Posts: 453
Joined: 2009-07-01 20:46
Contact:

Re: Deadlocks

Post by sheffters » 2009-10-27 17:53

I'm running hMailserver on two Windows 2003 Servers, one with hMailserver and the other with SQL Server 2000. Can you (or anyone else in the forums) help me debug this problem?
SQL Server locks dependent on how much RAM it has available on the machine, so if you're low on RAM then you will get more locks than a machine with plenty of RAM free. It's basically given up waiting for the lock to be released. Have this problem a lot in high transaction DBs on SQL Server (e.g. banking) ... but assume you have a much lower spec machine so the same scenario will happen with a much lower thoughput.

Either upgrade to a differet DB or add more RAM ... I think is the answer.
What happens to the messages in case of a failed DB write, does the message still get delivered?
Guessing a bit, but there won't be any record of it in the DB ... wether or not it gets delivered I don't know ... or if its updating the message flag to say sent then it may send it twice for example ... depends at what point SQL Server kicks it out ... will be random when this happens.

S.

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-27 18:04

Thanks for the quick reply!

The database server is indeed not the latest and greatest but it's definitely not out of memory. It currently has 2GB and SQL is taking up 500MB of that.

Could it be number of concurrent connections, and where do I check that (I'm thinking SQL Profiler.)

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-27 18:27

What version of SQL Server 2000 are you using (Standard, Enterprise, Developer) as locking options different between editions. If it's not 2 threads trying to update the same message row at the same time then you're running into a page or table lock issue, it might be worth running SQL Profiler and tracing to see what is happening when this occurs - find out what the other pid is that is involved in the deadlock and see if it's hMailServer doing this or something else.

Are you running any other applications that are reading the hMailServer data (reporting tools, etc)?

What service pack do you have installed for SQL Server 2000?

I'm using SQL Server 2005 Express on both my hMail servers and so far haven't seen any deadlocks occuring.

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-27 18:44

My vitals:
- SQL Server 2000 Standard (SP4)
- On the same DB server is a CMS which has about 30 websites running on it. (about 10000 hits a day combined)
- hMailserver install has 127 domains.

The weird thing is that this used to run fine before 5.0, I'm not blaming the update per se, it could be a million things, I also recently moved some of the websites to a different server (they were running on the same mail server before)

I just need some starting point.

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-27 18:53

Assuming that the websites aren't connecting the hMailServer database at all, the next thing I'd look at would be the SQL Server locking configuration. Without a trace showing the locking at the time these errors occur you're going to have a hard time nailing this down.

Do you have any trace flags enabled that might be causing row level locking to be disabled?

It's possible that there was a problem in the earlier versions you were running but that they were hidden (ie not reported in the log) but now you're on a newer version you can see the problem occurring.

You've also got a much larger system than mine, I've only got about 40 domains and most email is outgoing (sent about 40,000 out today, for instance). I'm also running 5.2 (am considering moving to 5.3 but keeping an eye on the forums to look out for any problems cropping up with it before taking the plunge). SQL Server 2005 also has an improved locking system so might also benefit here, but I appreciate that you can't just go shifting a large install to 2005 without any proof it'll fix this issue.

EDIT: Just a thought, when was the last time you ran a statistics update? It could be that your statistics are out date for the hm_messages table and it's using a page or table lock for updates rather than row level locking. An index rebuild (the PK for the table is the messageid index, but it's unclustered and so could well be badly fragmented) might help here, or it might just be a wild goose chase.

Can you also run some file stats queries and performance monitor and see if you've got an I/O bottleneck on the database files? If your server is not able to write to disk fast enough you might be running into issues with updates taking a long time to run and so triggering deadlocks as multiple processes are trying to make changes to the same messages.

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-27 19:10

Tracing all the locks being aquired/released is like drinking from 15 fire hoses... about 10,000 rows per second are added (most, if not all for hMailserver)

I've checked my firewall logs and the delivery queue of hMailserver and there's not much going on apart from a few users loging into pop3 and sending a couple of e-mails. (No spammer bot or the like)

edit: to be more precise; there are currently 2 smtp, 1 pop3 and 8 imap connections (according to hMail admin,) still hMail is going crazy with opening up locks (according to the SQL profiler)

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-27 19:30

Regarding your EDIT, the hMail database is set to auto update the statistics, so I've never really looked into doing that manually. I checked the hm_messages table and it has no PK (?), it does have to indexes, one on accountid coupled with messagefolderid (clustered) the other is messagetype... seems weird.

User avatar
sheffters
Senior user
Senior user
Posts: 453
Joined: 2009-07-01 20:46
Contact:

Re: Deadlocks

Post by sheffters » 2009-10-27 20:28

The weird thing is that this used to run fine before 5.0
v5 has a lot more database stuff in it I think (esp the later versions) ... maybe SQL Server is reaching some memory allocation limit if its not using all available RAM?

Don't know for sure, but most DBs have some form of max RAM allocation setting, presume theres one for SQL Server somewhere.

Cheers

S.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Deadlocks

Post by martin » 2009-10-27 20:38

hMailServer doesn't use rely on SQL Server transactions (except for the implict ones created by every statement). I don't think I've ever seen a case where a piece of software *not* relying on explicit transactions receive a deadlock. No-one has ever reported this since before.

Locks are made automatically whenever any statements are run by hMailServer. Even if you do a simple SELECT statement, you typically see lots of locks if you trace that.

What does the CPU usage on the SQL server look like? Is it constantly over 50%?

Roughly how many times have you seen the deadlocks per day?

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-27 23:14

Ok, I've stopped hMail, restarted SQL and started hMail again and things seem to have calmed down by doing that. So that leads me to believe that it has something to do with the connection between hMail and SQL...

Answers to Martin's questions:
  • CPU usage at the time was spikes between 20% and 80% not a constant line at 100%.
  • I got the locking errors a _lot_, 1 every 5 to 10 seconds or so. However none since I did the restart described above about 3.5 hours ago. I do still get the occasional "DALConnection - Reconnecting to the database" error, which I got a million of when the locking errors occurred as well.
I hope this restart fixes my problems for good and if not, I'll be back :)

Thanks for the quick and knowledgeable responses!
Mingo.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Deadlocks

Post by martin » 2009-10-27 23:44

Since hMailServer doesn't use explicit transaction during production (only during upgrades), every statement is executed individually. Because of that, a lost connection between hMailServer and MSSQL should not be able to cause a deadlock. If an unexpected error occurs in the database layer, hMailServer attempts to reconnect to the database server. This is the "Reconnecting to the database" line you were seeing in the log - an unexpected error occurred and hMailServer did a reconnect to the database to recover from the error.

Could you check the Windows event viewer to see if anything is reported? I've seen cases before (with another product than hMailServer), where MSSQL 2000 went havoc due to a bug in the locking mechanism. When this happened, SQL reported an error to the Application log in the Windows event log.

Also, can you check the Server log in SQL Server Enterprise Manager to see if anything was reported? (I'm not sure of the exact wording in SQL 2000, but in the tree to the left you should find server logs containing error messages). SQL Server reports some categories of errors to this log.

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 09:56

Hi Martin,

I've checked the Event Log and can't find much out of the ordinary in the last couple of days.
Two warnings at the time that I restarted SQL: SuperSocket info: (SpnRegister) : Error 1355.

This is in my SQL Server Log for our hMail DB:

Code: Select all

2009-10-20 11:48:09.14 	spid11	Starting up database 'eline_mail'.
2009-10-20 11:48:11.16 	spid11	3 transactions rolled forward in database 'eline_mail' (14).
2009-10-20 11:48:11.64 	spid11	0 transactions rolled back in database 'eline_mail' (14).
2009-10-20 11:48:11.66 	spid11	Recovery is checkpointing database 'eline_mail' (14)
2009-10-20 11:51:24.04 	spid65	Starting up database 'eline_mail'.
2009-10-21 01:01:25.57 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-22 01:01:30.92 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-23 01:01:26.75 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-24 01:01:27.19 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-25 01:01:30.58 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-26 01:01:06.39 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
2009-10-27 01:01:21.16 	backup	Database backed up: Database: eline_mail, creation date(time): 2006/12/08(02:13
I have changed something on our server before my last restart and that's the 'auto-close' option. I've unchecked them on both our CMS database and the hMail database, so maybe that helped?

Mingo.

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 11:35

mhagen wrote:Regarding your EDIT, the hMail database is set to auto update the statistics, so I've never really looked into doing that manually. I checked the hm_messages table and it has no PK (?), it does have to indexes, one on accountid coupled with messagefolderid (clustered) the other is messagetype... seems weird.
There's no PK (Primary Key)? I'm running 5.2 here and it has a PK (which is also a non-clustered index) on messageid, if this is missing in yours then you could be running in table locking issues due to SQL Server having to scan the entire table (and so introducing a table lock or multiple page locks) when searching for the messageid when performing these updates instead of using an index seek to find the one row affected and lock only that row.

In both SQL Enterprise Manager and SQL Server Management Studio the primary key should appear under the Indexes list. If you really don't have an index/PK on the messageid column I'd almost put money on adding it (make sure it's unclustered, default is normally clustered) will cure the deadlock issues you're seeing.

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 11:47

All hell broke loose again this morning when everybody started to login again (so my optimism last night was premature.) I've now added the PK to hm_messages and this seems to have helped, we'll see.

Is there a SQL script that (re-)adds all the right indexes and optimizations? I don't know how or when the key was removed...

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 12:08

I can generate a script from SQL Server 2005 but it'll probably fail on SQL Server 2000 due to changes in the index creation syntax (2005 added more options). If I get chance I could try to install a new copy on my local PC against one of my SQL Server 2000 systems and then generate the SQL to recreate everything that's missing for all the tables (indexes, constraints, and keys). Which specific version of hMail are you using?

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 12:40

Ah, right, good thinking I can do that myself! :)

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 12:42

Just remember to only script the indexes and keys - I learnt long ago (with SQL Server 6.5) not script out the whole database and then run it, saves quite a bit time when you don't have to restore the data! :P

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 13:15

Haha! I'm not a complete n00b but thanks for the heads-up :)

Server is running much better btw since the PK returned to hm_messages, got a different problem now though with removing a gig of email from an errors-catching account. Ah the life of a dba/developer/it-goto-guy/whatever hat 'they' put on me next ;)

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 13:34

mhagen wrote:Server is running much better btw since the PK returned to hm_messages,
Good to hear :)
mhagen wrote:got a different problem now though with removing a gig of email from an errors-catching account.
Ouch!
mhagen wrote:Ah the life of a dba/developer/it-goto-guy/whatever hat 'they' put on me next ;)
A life I've known well for the past 15 years at my current employer. We don't have an IT department any more, just me :P

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 13:43

Ok, I've diffed the SQL scripts of both our mx1 and mx2 (which was recently installed) and there were differences in 20 tables, of which most were PK's with CLUSTERED indexes. About 5 had some missing additional indexes.

I guess the update scripts miss some of the optimizations some times? I've been updating this installation since 2004.

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 13:52

I've never updated hMail (only started with 5.2 a few months ago, migrated off NTMail), but it certainly looks like that is what has happened.

You might want to add those PKs as unclustered if you have a large number of rows in the tables already, as otherwise you might have to wait while SQL Server rearranges all the data. While a clustered index might be a little quicker, a non-clustered index should at least allow an Index Scan (as opposed to an Index Seek with a clustered PK) rather than a Table Scan in the execution plans which should still help to avoid deadlocks.

(you probably know this already as a dba, but I thought I'd include the details in case anyone else decides to start indexes to their tables and wasn't familiar with how indexes are used).

mhagen
New user
New user
Posts: 21
Joined: 2005-05-06 14:05
Location: Amsterdam
Contact:

Re: Deadlocks

Post by mhagen » 2009-10-28 17:27

0 errors since last post, not even a 'Reconnecting to the database,' message. So this topic can be closed (if that's the way the forum is run.)

Martin, DanCML and sheffters thank you for the assistance.

DanCML
Normal user
Normal user
Posts: 103
Joined: 2009-07-08 17:56

Re: Deadlocks

Post by DanCML » 2009-10-28 17:47

Glad to hear it's fixed :D

huseyinalkan
New user
New user
Posts: 3
Joined: 2009-11-19 12:26

Re: Deadlocks

Post by huseyinalkan » 2009-11-19 15:04

hi,
We have been using hmail.
but have problems in sending to multiple.
We want to send mail 10000th per minute average.
how can we do?
this possible?

thanks

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Re: Deadlocks

Post by westdam » 2009-11-19 15:15

10000 mail per minute?? spammer sounds me...also it's OT.

huseyinalkan
New user
New user
Posts: 3
Joined: 2009-11-19 12:26

Re: Deadlocks

Post by huseyinalkan » 2009-11-19 15:54

no not spam.
They saved our customers.

User avatar
mattg
Moderator
Moderator
Posts: 20554
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Deadlocks

Post by mattg » 2009-11-19 16:03

Please don't re-use an existing thread
Please don't post the same question in multiple places.

(Answered in the new thread that you created... )http://www.hmailserver.com/forum/viewto ... =7&t=16833
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Locked