Hi,

I am having deadlock problem but I don't know what's causing it. My application is asp.net on IIS 7.5, MSSQL 2005, and DataObjects 4.6.8 I am using the sessionmanager to handle transactions..

Any ideas how I could solve this problem.

Thank you,

Richard

Exception type: DeadlockException

Exception message: SQL error occured. SQL error details 'Type: Deadlock;' Query 'INSERT INTO [dbo].[BaseEntity] ([Id], [TypeId], [Alias], [DoNotDisplayName], [Active], [ViewOrder], [Date], [PublishDate], [ExpirationDate], [Description], [FriendlyURL], [Featured], [Searchable], [DateAdded], [DateModified], [CreateBy.Id], [LastModifiedBy.Id]) VALUES (@p1_0, @p1_1, @p1_2, @p1_3, @p1_4, @p1_5, @p1_6, @p1_7, @p1_8, @p1_9, @p1_10, @p1_11, @p1_12, @p1_13, @p1_14, @p1_15, @p1_16); INSERT INTO [dbo].[UserSession] ([Id], [TypeId], [User.Id], [SessionAction], [SessionID], [LogOnAttemptResult], [LogOffMethod], [Browser], [UserAgent], [IPAddress], [Url]) VALUES (@p1_0, @p1_1, @p1_17, @p1_18, @p1_19, @p1_20, @p1_21, @p1_22, @p1_23, @p1_24, @p1_25); UPDATE [dbo].[BaseEntity] SET [DateModified] = @p2_0 WHERE ([BaseEntity].[Id] = @p2_1); UPDATE [dbo].[User] SET [SessionID] = @p2_2, [LoggedOn] = @p2_3, [LastLogon] = @p2_4, [LastLogOnAttempt] = @p2_5 WHERE ([User].[Id] = @p2_1); SELECT TOP 1 [a].[Id], [a].[TypeId], [a].[Alias], [a].[DoNotDisplayName], [a].[Active], [a].[ViewOrder], [a].[Date], [a].[PublishDate], [a].[ExpirationDate], [a].[Description], [a].[FriendlyURL], [a].[Featured], [a].[Searchable], [a].[DateAdded], [a].[DateModified], [a].[CreateBy.Id], [a].[LastModifiedBy.Id], [b].[CI.Id], [b].[Agency.Id], [b].[CancelUser.Id], [b].[CancelDate], [b].[Notifcation_Sent_To_CI] FROM [dbo].[BondingVisit] [b] INNER JOIN [dbo].[BaseEntity] [a] ON ([a].[Id] = [b].[Id]) WHERE ([a].[Id] = 219674) ORDER BY [a].[Id] ASC; [p1_0='254309';p1_1='139';p1_2='';p1_3='False';p1_4='False';p1_5='0';p1_6='1/1/1753 12:00:00 AM';p1_7='1/1/1753 12:00:00 AM';p1_8='1/1/1753 12:00:00 AM';p1_9='';p1_10='';p1_11='False';p1_12='';p1_13='12/18/2015 10:28:32 AM';p1_14='12/18/2015 10:28:32 AM';p1_15='';p1_16='';p1_17='99711';p1_18='Logon';p1_19='5b12vhyvevf53oamyvslbbws';p1_20='LogonSuccessful';p1_21='';p1_22='InternetExplorer version 11.0';p1_23='Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko';p1_24='205.145.233.1';p1_25='https://coibv.childrenofinmates.org/Logon.aspx?ReturnUrl=[%]2fApp[%]2fBV_CI_Dashboard.aspx[%]3fv[%]3ddWlkPTk5NzExJmlkPTIxOTY3NCZ0PVYmYXQ9b0RZWlczSE5lczdmYkhCdDJFTTFvOFZLSExUbGtJZjZxeG1pdlAyZjFQND0[%]3d-j34uWPMZURo[%]3d&v=dWlkPTk5NzExJmlkPTIxOTY3NCZ0PVYmYXQ9b0RZWlczSE5lczdmYkhCdDJFTTFvOFZLSExUbGtJZjZxeG1pdlAyZjFQND0=-j34uWPMZURo=';p2_0='12/18/2015 10:28:32 AM';p2_1='99711';p2_2='5b12vhyvevf53oamyvslbbws';p2_3='True';p2_4='12/18/2015 10:28:32 AM';p2_5='12/18/2015 10:28:32 AM']' Original message 'Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.' at Xtensive.Orm.Providers.StorageDriver.ExecuteCommandTResult at Xtensive.Core.DelegateBindExtensions.<>c__DisplayClassd6.<Bind>b__c(T2 arg2, T3 arg3, T4 arg4, T5 arg5) at Xtensive.Orm.Linq.QueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source) at NetString.RDFLib.Data.QueryProcessor.GetFirstOrDefaultT in c:\Program Develop.Net V4.5.1\Projects\NetString.RDFLib\NetString.RDFLib\Data\QueryProcessor.cs:line 68 at NetString.RDFLib.Data.DataService.BaseService.GetEntityByIDT in c:\Program Develop.Net V4.5.1\Projects\NetString.RDFLib\NetString.RDFLib\Data\DataService\BaseService.cs:line 48 at COIBV.DAL.DataService.BVService.GetBVByID(Int32 id) in c:\Program Develop.Net V4.5.1\Projects\COIBV.DAL\COIBV.DAL\DataService\BVService.cs:line 182 at Admin_Logon.UpdateCIStaff_AccessDate(Int32 BV_ID, BVUser User) in e:\WebSites\COIBV\Logon.aspx.cs:line 222 at Admin_Logon.Process_CIStaff_Auto_Logon() in e:\WebSites\COIBV\Logon.aspx.cs:line 158 at Admin_Logon.Page_Load(Object sender, EventArgs e) in e:\WebSites\COIBV\Logon.aspx.cs:line 37 at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at Xtensive.Orm.Providers.StorageDriver.ExecuteCommandTResult

Request information: Request URL: https://coibv.childrenofinmates.org:443/Logon.aspx?ReturnUrl=/App/BV_CI_Dashboard.aspx?v=dWlkPTk5NzExJmlkPTIxOTY3NCZ0PVYmYXQ9b0RZWlczSE5lczdmYkhCdDJFTTFvOFZLSExUbGtJZjZxeG1pdlAyZjFQND0=-j34uWPMZURo=&v=dWlkPTk5NzExJmlkPTIxOTY3NCZ0PVYmYXQ9b0RZWlczSE5lczdmYkhCdDJFTTFvOFZLSExUbGtJZjZxeG1pdlAyZjFQND0=-j34uWPMZURo= Request path: /Logon.aspx User host address: 205.145.233.1 User: Is authenticated: False Authentication Type: Thread account name: IIS APPPOOL\COIBV

Thread information: Thread ID: 22 Thread account name: IIS APPPOOL\COIBV Is impersonating: False Stack trace: at Xtensive.Orm.Providers.StorageDriver.ExecuteCommandTResult at Xtensive.Core.DelegateBindExtensions.<>c__DisplayClassd6.<Bind>b__c(T2 arg2, T3 arg3, T4 arg4, T5 arg5) at Xtensive.Orm.Linq.QueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source) at NetString.RDFLib.Data.QueryProcessor.GetFirstOrDefaultT in c:\Program Develop.Net V4.5.1\Projects\NetString.RDFLib\NetString.RDFLib\Data\QueryProcessor.cs:line 68 at NetString.RDFLib.Data.DataService.BaseService.GetEntityByIDT in c:\Program Develop.Net V4.5.1\Projects\NetString.RDFLib\NetString.RDFLib\Data\DataService\BaseService.cs:line 48 at COIBV.DAL.DataService.BVService.GetBVByID(Int32 id) in c:\Program Develop.Net V4.5.1\Projects\COIBV.DAL\COIBV.DAL\DataService\BVService.cs:line 182 at Admin_Logon.UpdateCIStaff_AccessDate(Int32 BV_ID, BVUser User) in e:\WebSites\COIBV\Logon.aspx.cs:line 222 at Admin_Logon.Process_CIStaff_Auto_Logon() in e:\WebSites\COIBV\Logon.aspx.cs:line 158 at Admin_Logon.Page_Load(Object sender, EventArgs e) in e:\WebSites\COIBV\Logon.aspx.cs:line 37 at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

asked Dec 18 '15 at 16:37

rasxte's gravatar image

rasxte
20161617

edited Dec 18 '15 at 20:06

Just added a comment to the first answer. Adding another one here to make sure you saw it :) Good luck with resolving the issue!

(Dec 24 '15 at 04:49) Alex Yakunin Alex%20Yakunin's gravatar image

One Answer:

Hello Richard

I assume that at least two types are inherited from BaseEntity. I think the sittuation when two or more threads open its own transaction and change some descendants of BaseEntity may lead you to deadlock. All the descendants might change rows of BaseEntity, at least ModifiedDate column. Do you realy need to have BaseEntity as table in database?

answered Dec 21 '15 at 06:23

Alexey%20Kulakov's gravatar image

Alexey Kulakov
63215

Hi Alexey,

Thank for your response..

Actually, all of the entities are derived from baseentity... The issue occurs during having volume.... Do you have any other solution... maybe retrying to reprocess the request.. I am not sure if that would work..

Thank you,

Richard

(Dec 21 '15 at 20:51) rasxte rasxte's gravatar image

Please, make sure that the source of problem is multithreading access to BaseEntity, first. If it is, then you will have to rework your architecture somehow to not create BaseEntity table in database. I think that reprocessing is an option but only for some time, because in future if users access to your app more frequently the exception will annoy you much more often than now.

(Dec 24 '15 at 03:21) Alexey Kulakov Alexey%20Kulakov's gravatar image

Hi, original DO author is here :) Another option you might consider is to turn on Snapshot Isolation for your DB. SQL Server 2005 is the first SQL Server version that supports this mode, so you shouldn't have issues with turning it on; on the other hand, you might also consider upgrading SQL Server -- I am pretty sure that in later versions Microsoft has made a set of improvements related to MVCC, so relying on some later version might be a better option anyway.

As for why it should address the issue, please check out https://en.wikipedia.org/wiki/Multiversion_concurrency_control

(Dec 24 '15 at 04:44) Alex Yakunin Alex%20Yakunin's gravatar image

Currently we don't recommend to use non-snapshot modes in any case: lock-based isolation isn't scalable at all. Typically problems arise when you have some kind of reporting load mixed with the write load. In this case a single reader that touches a lot of rows completely blocks any writes, and this issue alone dramatically increases the chance of getting a deadlock.

(Dec 24 '15 at 04:48) Alex Yakunin Alex%20Yakunin's gravatar image

I have come up with a possible solution. I think the problem in this particular instance is that the current web request consisted on authenticating the request and updating a baseentity field. The request was redirected to a web page that does grid binding and it takes a long time, enough to cause the deadlock for subsequent request for authentication. So perhaps that's the reason for the deadlock.

continue on next comment...

(Dec 24 '15 at 09:46) rasxte rasxte's gravatar image

Since I am relying on the sessionmanager, in the authentication web page I created a local transaction to release the baseentity field rather than relying on the sessiomanager global transaction . I am not sure if this will work but I hope it does.. Otherwise changing the whole design of my datamodel would be crazy:)

Thank your cooperation,

Richard

(Dec 24 '15 at 09:46) rasxte rasxte's gravatar image

I don't think you have to change the whole design. What you should find out is what causes this deadlock - that's it.

(Dec 25 '15 at 05:08) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×5

Asked: Dec 18 '15 at 16:37

Seen: 3,133 times

Last updated: Dec 25 '15 at 05:08

powered by OSQA