NetTalk Central
NetTalk Web Server => Web Server - Ask For Help => Topic started by: kevin plummer on November 04, 2009, 05:22:32 PM
-
Hi All,
I seem to be experiencing a lot of deadlocks on my web app.
I have a MSSQL2005 DB and I have enabled MARS on the web app.
I have 5 web apps accessing this DB as well as 2-3 clarion windows apps which are not MARS enabled.
If I get a deadlock, killing the web app instantly fixes the problem.
Anyone else come across this prob and any advice? I have never come across this on the windows apps so I think it is something to do with MARS and how things are processed on the
Cheers,
Kevin
-
I wonder if this is the culprit inside a SP?
Select @L_Value = TRA_RecordID From TRANS where TRA_ID = 1
Set @L_NewValue = @L_Value + 1
Update TRANS Set TRA_RecordID = @L_NewValue Where TRA_ID = 1
Return @L_Value
-
Looking at SQL Profler I think it is poor design on my part.
I'm going to make the ID fields Identity so SQL seeds them rather than myself - this will reduce the number of select/updates on that table.
Kev
-
Interestingly, I found this earlier this week:
http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx
==================================
The following pattern typically stems from an old practice used in SQL 4.x and 6.x days, before IDENTITY was introduced.
begin tran
declare @iVal int
select @iVal = iVal from CounterTable (HOLDLOCK) where CounterName = 'CaseNumber'
update CounterTable
set iVal = @iVal + 1
where CounterName = 'CaseNumber'
commit tran
return @iVal
This can be a dangerous construct. Assume that the query is cancelled (attention) right after the select. SQL Server treats this as a batch termination and does not execute other statements. The application now holds a lock under the open transaction and without proper handling it leads to blocking.
One Statement Fix
declare @iVal int
update CounterTable
set @iVal = iVal = iVal + 1
where CounterName = 'CaseNumber'
return @iVal
SQL Server allows you to update a value and return the updated value into a variable. A single update statement can do all the work. If the query is cancelled by the application the update is atomic and will complete or rollback but the application has much better control over the lock and transaction scope.
Use this to help reduce blocking and possible deadlock issues. Since the lookup only takes place one time it can also increase performance of you application.
-
Thanks John - that's a quick fix which will hopefully get me out of the muck until I can get the Identity changes done.
Cheers,
Kevin
-
UPDATE:
I did the quick and it seemed to go well but still got a deadlock. So I fixed the problem properly over the weekend adding the Identity field to all the relevant tables, converting the DB Tables etc etc.
I was still getting the deadlock with an internal user using IE. I had them change to FF and no more problems. I asked all external users to change to FF. I got another deadlock but that user was still using IE. They moved to FF and so far so good.
So I'm not sure my original diagnosis was right as that should not matter what browser they are using. right?
Kevin
-
Hi Kevin,
no, it shouldn't matter.
Unless maybe there was an old web file or something in their cache?
cheers
Bruce