October 2, 2015 at 1:11 am
Eirikur Eiriksson (10/2/2015)
Lynn Pettis (10/2/2015)
There are no foreign key relationships between any of the tables in the database. There is no table above the base report tables (OperationsSIGACTDraftReport OperationsSIGACTPublishedReport, etc). There are subtables to the base tables, but as I said, no FKs are defined.We can rule that one out then. Another question, where is the read uncommited isolation level set for the second (processa1b3288) and the third (processa1e6bc8) process? Just a hunch, seen similar causing deadlocks.
😎
Could be, they use that isolation level a lot in the code. A few places I can understand, like the code used to populate the dashboards where close enough is good enough because it has to be fast.
October 2, 2015 at 1:31 am
Looking at the DG again, I think the Workflow table has nothing to do with the deadlock, it is just part of one of the implicit transactions that are trying to update and delete from the OperationsSIGACTDraftReport table.
😎
October 2, 2015 at 6:29 am
Stupid question, which may or may not have anything to do with this...
You mention it starts when 6 ore more users are logged in. Is the program utilizing linked servers which may have logins specified? Or could they all be logging in with a common login that may be holding phantom locks on the tables?
October 2, 2015 at 9:02 am
Brandie Tarvin (10/2/2015)
Stupid question, which may or may not have anything to do with this...You mention it starts when 6 ore more users are logged in. Is the program utilizing linked servers which may have logins specified? Or could they all be logging in with a common login that may be holding phantom locks on the tables?
No linked servers. The application uses a single login to access the database. That does happen to be one of the rabbit holes I need to go down as we do think that it could be connection pooling but I haven't heard anything back regarding Apache/Tomcat as of yet. So, no Brandie, not a stupid question at all.
October 8, 2015 at 3:52 pm
Now, there has always been something that bothered me with this application and that is the mismatch in data types that seems to occur. Well, there is a configuration setting in ColdFusion, String Format, that makes all strings to be treated as NVARCHAR. Well, what happens if your primary key is varchar(36) and you send a nvarchar value? One, you don't get to use the index on the table. Now this doesn't mean you get a table scan either. In our case, SQL Server chose to scan a NC index (idx_DateInitialPublished for example) on the OperationsSIGACTDraftReport table. It is a small index with only a datetime value and the ReportKey. Once it found the ReportKey it needed it would then do a bookmark lookup to the clustered index.
Well, multiply it to most of our tables.
Today, the engineer changed this setting so that we were sending varchar values. The automated test suite has been running for over an hour and no deadlocks. Java is running happily. And we have very few scans on the indexes on the two tables we were getting deadlocks.
Moral: Use proper data types. And if anyone else runs into bizarre deadlock issues like this, check your data types from the application to the database.
FYI, when this application was first being developed in Iraq, they had no real database developer.
October 9, 2015 at 4:58 am
Wow. That is good information to have. Thanks for the update, Lynn. I'd never have considered something like that.
October 9, 2015 at 10:01 am
Test suite ran all night, no deadlocks. Index stats on the two tables are really nice, mostly seeks, lookups next, then some scans. Much better than before when we had thousands of scans.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply