search

OrchardCMS / Orchard

Orchard is a free, open source, community-focused Content Management System built on the ASP.NET MVC platform.
https://orchardproject.net
BSD 3-Clause "New" or "Revised" License
2.38k stars 1.12k forks source link

Database Deadlock v1.8.1 #5095

Open orchardbot opened 9 years ago

orchardbot commented 9 years ago

babakarj created: https://orchard.codeplex.com/workitem/21266

Using Orchard version 1.8.1, we get following deadlock on concurrent usage:

1.

isolationlevel="read committed (2)" lockMode="S" lockTimeout="4294967295" status="suspended"

(@p0 int,@p1 bit) SELECT this_.Id as Id482, this_.Number as Number482, this_.Published as Published482, this_.Latest as Latest482, this_.Data as Data482, this_.ContentItemRecord_id as ContentI6_482, contentite1_.Id as Id470, contentite1_.Data as Data470, contentite1_.ContentType_id as ContentT3_470, contenttyp4_.Id as Id491, contenttyp4_.Name as Name491 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_FrameworkContentItemRecord contentite1 on this_.ContentItemRecordid=contentite1.Id left outer join Orchard_FrameworkContentTypeRecord contenttyp4 on contentite1_.ContentTypeid=contenttyp4.Id WHERE contentite1.Id = @p0 and this.Latest = @p1

(@p0 nvarchar(max) ,@p1 int,@p2 int) UPDATE Orchard_Framework_ContentItemRecord SET Data = @p0, ContentType_id = @p1 WHERE Id = @p2 2.

lockMode="S" lockTimeout="4294967295" isolationlevel="read committed (2)" status="suspended"

(@p0 nvarchar(4000),@p1 bit) SELECT this_.Id as Id483, this_.Number as Number483, this_.Published as Published483, this_.Latest as Latest483, this_.Data as Data483, this_.ContentItemRecord_id as ContentI6_483, autoroutep1_.Id as Id1160, autoroutep1_.UseCustomPattern as UseCusto2_1160, autoroutep1_.CustomPattern as CustomPa3_1160, autoroutep1_.DisplayAlias as DisplayA4_1160, autoroutep1_.ContentItemRecord_id as ContentI5_1160, contentite4_.Id as Id471, contentite4_.Data as Data471, contentite4_.ContentType_id as ContentT3_471, contenttyp5_.Id as Id492, contenttyp5_.Name as Name492 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_AutorouteAutoroutePartRecord autoroutep1 on this.Id=autoroutep1.Id left outer join Orchard_FrameworkContentItemRecord contentite4 on this_.ContentItemRecordid=contentite4.Id left outer join Orchard_FrameworkContentTypeRecord contenttyp5 on contentite4_.ContentTypeid=contenttyp5.Id WHERE (autoroutep1_.DisplayAlias is no

(@p0 nvarchar(4000),@p1 bit) SELECT this_.Id as Id483, this_.Number as Number483, this_.Published as Published483, this_.Latest as Latest483, this_.Data as Data483, this_.ContentItemRecord_id as ContentI6_483, autoroutep1_.Id as Id1160, autoroutep1_.UseCustomPattern as UseCusto2_1160, autoroutep1_.CustomPattern as CustomPa3_1160, autoroutep1_.DisplayAlias as DisplayA4_1160, autoroutep1_.ContentItemRecord_id as ContentI5_1160, contentite4_.Id as Id471, contentite4_.Data as Data471, contentite4_.ContentType_id as ContentT3_471, contenttyp5_.Id as Id492, contenttyp5_.Name as Name492 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_AutorouteAutoroutePartRecord autoroutep1 on this.Id=autoroutep1.Id left outer join Orchard_FrameworkContentItemRecord contentite4 on this_.ContentItemRecordid=contentite4.Id left outer join Orchard_FrameworkContentTypeRecord contenttyp5 on contentite4_.ContentTypeid=contenttyp5.Id WHERE (autoroutep1_.DisplayAlias is no

Any idea how to resolve this problem?

Log: 2015-03-14 13:53:06,652 [57] Orchard.Exceptions.DefaultExceptionPolicy - Default - An unexpected exception was caught http://norouz.mega.net/Admin/Contents/Edit/17265 NHibernate.Exceptions.GenericADOException: could not execute query [ SELECT this_.Id as Id472, this_.Number as Number472, this_.Published as Published472, this_.Latest as Latest472, this_.Data as Data472, this_.ContentItemRecord_id as ContentI6_472, contentite1_.Id as Id460, contentite1_.Data as Data460, contentite1_.ContentType_id as ContentT3_460, contenttyp4_.Id as Id481, contenttyp4_.Name as Name481 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_FrameworkContentItemRecord contentite1 on this_.ContentItemRecordid=contentite1.Id left outer join Orchard_FrameworkContentTypeRecord contenttyp4 on contentite1_.ContentTypeid=contenttyp4.Id WHERE contentite1.Id = @p0 and this.Latest = @p1 ] Name:cp0 - Value:17265 Name:cp1 - Value:True [SQL: SELECT this_.Id as Id472, this_.Number as Number472, this_.Published as Published472, this_.Latest as Latest472, this_.Data as Data472, this_.ContentItemRecord_id as ContentI6_472, contentite1_.Id as Id460, contentite1_.Data as Data460, contentite1_.ContentType_id as ContentT3_460, contenttyp4_.Id as Id481, contenttyp4_.Name as Name481 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_FrameworkContentItemRecord contentite1 on this_.ContentItemRecordid=contentite1.Id left outer join Orchard_FrameworkContentTypeRecord contenttyp4 on contentite1_.ContentTypeid=contenttyp4.Id WHERE contentite1.Id = @p0 and this.Latest = @p1] ---> System.Data.SqlClient.SqlException: Transaction (Process ID 72) 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.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at NHibernate.Driver.NHybridDataReader.Read() in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Driver\NHybridDataReader.cs:line 116 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 487 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 251 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1564 --- End of inner exception stack trace --- at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1581 at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1472 at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 76 at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:line 1984 at NHibernate.Impl.CriteriaImpl.List(IList results) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\CriteriaImpl.cs:line 265 at NHibernate.Impl.CriteriaImpl.List[T]() in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\CriteriaImpl.cs:line 277 at Orchard.ContentManagement.DefaultContentManager.GetManyImplementation(QueryHints hints, Action2 predicate) in d:\Projects\norouz\trunk\src\Orchard\ContentManagement\DefaultContentManager.cs:line 359 at Orchard.ContentManagement.DefaultContentManager.Get(Int32 id, VersionOptions options, QueryHints hints) in d:\Projects\norouz\trunk\src\Orchard\ContentManagement\DefaultContentManager.cs:line 160 at Orchard.Core.Contents.Controllers.AdminController.Edit(Int32 id) in d:\Projects\norouz\trunk\src\Orchard.Web\Core\Contents\Controllers\AdminController.cs:line 273 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.b39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>cDisplayClass48.b41() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>cDisplayClass48.b41()

orchardbot commented 9 years ago

@sebastienros commented:

Could you provide some repro steps?

orchardbot commented 9 years ago

babakarj commented:

Yes. Login with 2 or more different users in admin panel. Then try to update any content concurrently. You'll get the error after few tries. In this case one of those users get the error and his content disappears from the list. We should manually update the database and set his content to published by an update SQL query.

Currently, we solve the problem by changing the isolation level to read-uncommitted. But we know it is not the solution.

orchardbot commented 9 years ago

@Piedone commented:

It would be interesting to know what are the queries before the deadlocks happen, i.e. what causes the deadlock.

Don't you have Taxonomies Field attached to the content types in question? Because in 1.8.x I fixed an issue with this that should lower the chance of related deadlocks.

orchardbot commented 9 years ago

babakarj commented:

Let me know how can we provide you with more information. And, no, we don't use or define Taxonomies.

orchardbot commented 9 years ago

@Piedone commented:

It curiously looks like a deadlock on ContentItemVersionRecord which would be really problematic... I don't really know how but there is some deadlock log or something in SQL Server that you can use to detect which queries clash with each other.

mberacochea commented 9 years ago

Hi we are having the same issue :

NHibernate.Exceptions.GenericADOException: could not execute query [ SELECT this_.Id as Id612, this_.Number as Number612, this_.Published as Published612, this_.Latest as Latest612, this_.Data as Data612, this_.ContentItemRecord_id as ContentI6_612, contentite1_.Id as Id600, contentite1_.Data as Data600, contentite1_.ContentType_id as ContentT3_600, contenttyp2_.Id as Id621, contenttyp2_.Name as Name621 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_FrameworkContentItemRecord contentite1 on this_.ContentItemRecordid=contentite1.Id inner join Orchard_FrameworkContentTypeRecord contenttyp2 on contentite1_.ContentTypeid=contenttyp2.Id WHERE contenttyp2.Name = @p0 and this.Published = @p1 ] Name:cp0 - Value:Layer Name:cp1 - Value:True [SQL: SELECT this_.Id as Id612, this_.Number as Number612, this_.Published as Published612, this_.Latest as Latest612, this_.Data as Data612, this_.ContentItemRecord_id as ContentI6_612, contentite1_.Id as Id600, contentite1_.Data as Data600, contentite1_.ContentType_id as ContentT3_600, contenttyp2_.Id as Id621, contenttyp2_.Name as Name621 FROM Orchard_FrameworkContentItemVersionRecord this inner join Orchard_FrameworkContentItemRecord contentite1 on this_.ContentItemRecordid=contentite1.Id inner join Orchard_FrameworkContentTypeRecord contenttyp2 on contentite1_.ContentTypeid=contenttyp2.Id WHERE contenttyp2.Name = @p0 and this.Published = @p1] ---> System.Data.SqlClient.SqlException: Transaction (Process ID 211) 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.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at NHibernate.Driver.NHybridDataReader.Read() in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Driver\NHybridDataReader.cs:line 116 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 487 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 251 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1564 --- End of inner exception stack trace --- at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1581 at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1472 at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 76 at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:line 1984 at NHibernate.Impl.CriteriaImpl.List(IList results) in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\CriteriaImpl.cs:line 265 at NHibernate.Impl.CriteriaImpl.List[T]() in c:\Users\sebros\Documents\My Projects\nhibernate-core\src\NHibernate\Impl\CriteriaImpl.cs:line 277 at Orchard.ContentManagement.DefaultContentQuery.Slice(Int32 skip, Int32 count) in d:\wwwroot\web.oef.com\halo18\repo\Site\src\Orchard\ContentManagement\DefaultContentQuery.cs:line 161 at Orchard.ContentManagement.DefaultContentQuery.ContentQuery1.Orchard.ContentManagement.IContentQuery.List() in d:\wwwroot\web.oef.com\halo18\repo\Site\src\Orchard\ContentManagement\DefaultContentQuery.cs:line 262 at Orchard.Widgets.Filters.WidgetFilter.OnResultExecuting(ResultExecutingContext filterContext) at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList`1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) 2015-04-20 15:14:44,846 [37] Orchard.Data.SessionLocator - Default - Error while disposing the transaction.

Temp fix

We are using IsolationLevel Snapshot to avoid this error. Is this Isolation Level sercure enough for Orchard?.

Thanks

pszmyd commented 9 years ago

Yes, it's secure.

Snapshot isolation level is considered a good practice in web applications, actually. We don't use it by default for a simple reason - it requires specific database settings (snapshot isolation is turned off by default) that need to be done manually. Plus, there are compatibility issues.

If it works in your specific setup, then it's perfectly ok to change it. You could also turn on READ COMMITTED SNAPSHOT mode on your server - you wouldn't have to change any code then:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
mberacochea commented 9 years ago

We encountered an error:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Orchard_Framework_ContentItemVersionRecord' directly or indirectly in database 'DB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

We are using Readuncommited now :(

Ermesx commented 9 years ago

I have the same problem in my application and we also have Read Uncommitted isolation level.

It works correct for almost all cases but causes problems with background and scheduled tasks when you use load balancing environment. Tasks ues record locking in DB to synchronize between instances of Orchard but Read Uncommitted remove that limit and is possible to run the same task twice or more times.

create-the-web commented 9 years ago

I have a similar problem with an older version of Orchard (1.7.) - deadlocks also occur with several users updating content items concurrently.

@Piedone: we tried to follow the hint with the deadlock log in sql server, the following two statements always lead to the deadlock (one select statement of a content item and an update statement on version records):

2015-04-07 10:01:33.52 spid23s     deadlock-list
2015-04-07 10:01:33.52 spid23s      deadlock victim=process3fe94c8
2015-04-07 10:01:33.52 spid23s       process-list
2015-04-07 10:01:33.52 spid23s        process id=process3fe94c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057594046709760 (0b130b678163) waittime=2880 ownerId=217922163 transactionname=user_transaction lasttranstarted=2015-04-07T10:01:30.637 XDES=0xd264f950 lockMode=S schedulerid=5 kpid=11988 status=suspended spid=56 sbid=2 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-04-07T10:01:30.640 lastbatchcompleted=2015-04-07T10:01:30.623 clientapp=.Net SqlClient Data Provider hostname=N156 hostpid=1764 loginname=Dz_Caravanmarkt_Info_LOGIN isolationlevel=read committed (2) xactid=217922163 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2015-04-07 10:01:33.52 spid23s         executionStack
2015-04-07 10:01:33.52 spid23s          frame procname=adhoc line=1 stmtstart=66 sqlhandle=0x02000000bfeb4635929e11ba9d5afa6b2c0966e924e3405d
2015-04-07 10:01:33.52 spid23s     SELECT this_.Id as Id31_3_, this_.Number as Number31_3_, this_.Published as Published31_3_, this_.Latest as Latest31_3_, this_.Data as Data31_3_, this_.ContentItemRecord_id as ContentI6_31_3_, autoroutep3_.Id as Id87_0_, autoroutep3_.UseCustomPattern as UseCusto2_87_0_, autoroutep3_.CustomPattern as CustomPa3_87_0_, autoroutep3_.DisplayAlias as DisplayA4_87_0_, autoroutep3_.ContentItemRecord_id as ContentI5_87_0_, contentite1_.Id as Id30_1_, contentite1_.Data as Data30_1_, contentite1_.ContentType_id as ContentT3_30_1_, entrypartr5_.Id as Id53_2_, entrypartr5_.AmWochenendeErreichbar as AmWochen2_53_2_, entrypartr5_.AnsprechpartnerAnrede as Ansprech3_53_2_, entrypartr5_.AnsprechpartnerNachname as Ansprech4_53_2_, entrypartr5_.AnsprechpartnerVorname as Ansprech5_53_2_, entrypartr5_.Auenreinigung as Auenrein6_53_2_, entrypartr5_.AusgestellteZelte as Ausgeste7_53_2_, entrypartr5_.Ausstellungsfahrzeuge as Ausstell8_53_2_, entrypartr5_.Autobahn as Autobahn53_2_, entrypartr5_.Campingshop as Camping10_53_2_, entrypar     
2015-04-07 10:01:33.52 spid23s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2015-04-07 10:01:33.52 spid23s     unknown     
2015-04-07 10:01:33.52 spid23s         inputbuf
2015-04-07 10:01:33.52 spid23s     (@p0 int,@p1 int,@p2 int,@p3 bit)SELECT this_.Id as Id31_3_, this_.Number as Number31_3_, this_.Published as Published31_3_, this_.Latest as Latest31_3_, this_.Data as Data31_3_, this_.ContentItemRecord_id as ContentI6_31_3_, autoroutep3_.Id as Id87_0_, autoroutep3_.UseCustomPattern as UseCusto2_87_0_, autoroutep3_.CustomPattern as CustomPa3_87_0_, autoroutep3_.DisplayAlias as DisplayA4_87_0_, autoroutep3_.ContentItemRecord_id as ContentI5_87_0_, contentite1_.Id as Id30_1_, contentite1_.Data as Data30_1_, contentite1_.ContentType_id as ContentT3_30_1_, entrypartr5_.Id as Id53_2_, entrypartr5_.AmWochenendeErreichbar as AmWochen2_53_2_, entrypartr5_.AnsprechpartnerAnrede as Ansprech3_53_2_, entrypartr5_.AnsprechpartnerNachname as Ansprech4_53_2_, entrypartr5_.AnsprechpartnerVorname as Ansprech5_53_2_, entrypartr5_.Auenreinigung as Auenrein6_53_2_, entrypartr5_.AusgestellteZelte as Ausgeste7_53_2_, entrypartr5_.Ausstellungsfahrzeuge as Ausstell8_53_2_, entrypartr5_.Autobahn as Autobahn53_2_, entrypartr5_.Campin    
2015-04-07 10:01:33.52 spid23s        process id=process3fb4e08 taskpriority=0 logused=572 waitresource=KEY: 9:72057594046709760 (126b4e7fa0d3) waittime=2830 ownerId=217921442 transactionname=user_transaction lasttranstarted=2015-04-07T10:01:28.523 XDES=0x9cad2e80 lockMode=X schedulerid=3 kpid=8552 status=suspended spid=61 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-04-07T10:01:30.693 lastbatchcompleted=2015-04-07T10:01:30.693 clientapp=.Net SqlClient Data Provider hostname=N156 hostpid=1764 loginname=Dz_Caravanmarkt_Info_LOGIN isolationlevel=read committed (2) xactid=217921442 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2015-04-07 10:01:33.52 spid23s         executionStack
2015-04-07 10:01:33.52 spid23s          frame procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000067dbdb291ae1dd1076318b495283eba949d93cb0
2015-04-07 10:01:33.52 spid23s     UPDATE Orchard_Framework_ContentItemVersionRecord SET Number = @p0, Published = @p1, Latest = @p2, Data = @p3, ContentItemRecord_id = @p4 WHERE Id = @p5     
2015-04-07 10:01:33.52 spid23s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2015-04-07 10:01:33.52 spid23s     unknown     
2015-04-07 10:01:33.52 spid23s         inputbuf
2015-04-07 10:01:33.52 spid23s     (@p0 int,@p1 bit,@p2 bit,@p3 nvarchar(max) ,@p4 int,@p5 int)UPDATE Orchard_Framework_ContentItemVersionRecord SET Number = @p0, Published = @p1, Latest = @p2, Data = @p3, ContentItemRecord_id = @p4 WHERE Id = @p5    
2015-04-07 10:01:33.52 spid23s       resource-list
2015-04-07 10:01:33.52 spid23s        keylock hobtid=72057594046709760 dbid=9 objectname=Dz_Caravanmarkt_Info.dbo.Orchard_Framework_ContentItemVersionRecord indexname=PK__Orchard_Framework_ContentItemVersionRecord__00000000000000D1 id=lockaecfb300 mode=X associatedObjectId=72057594046709760
2015-04-07 10:01:33.52 spid23s         owner-list
2015-04-07 10:01:33.52 spid23s          owner id=process3fb4e08 mode=X
2015-04-07 10:01:33.52 spid23s         waiter-list
2015-04-07 10:01:33.52 spid23s          waiter id=process3fe94c8 mode=S requestType=wait
2015-04-07 10:01:33.52 spid23s        keylock hobtid=72057594046709760 dbid=9 objectname=Dz_Caravanmarkt_Info.dbo.Orchard_Framework_ContentItemVersionRecord indexname=PK__Orchard_Framework_ContentItemVersionRecord__00000000000000D1 id=lock90231e00 mode=S associatedObjectId=72057594046709760
2015-04-07 10:01:33.52 spid23s         owner-list
2015-04-07 10:01:33.52 spid23s          owner id=process3fe94c8 mode=S
2015-04-07 10:01:33.52 spid23s         waiter-list
2015-04-07 10:01:33.52 spid23s          waiter id=process3fb4e08 mode=X requestType=wait

Our code creates a new draft version of a content item which may be published later in the same request: 

var ci = contentManager.Get(id, VersionOptions.DraftRequired);
// ... if certain conditions are met...
contentManager.Publish(ci);

The problem normally occurs when many users work simultaneously on the admin page.

Ermesx commented 8 years ago

Any news ?

carlwoodhouse commented 8 years ago

We're having this issue in 1.9.2 as well,

To reproduce - Added a new record backed contentpart to a contentype that already has a load of contentitems, visit them concurrently with some kind of load tester. (this will add backing records for the contentitem into the new record backing table for the contentpart.

Eventually it falls over because of deadlock.

carlwoodhouse commented 8 years ago

Soooo ... this was on a development db, our production uses sqlazure which by default has READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION OFF - by setting the isolation level on the dev db to mimic azure it fixed our issue.

That been said, there's probably still an underlying issue here as it should really work without read_commited_snapshot isolation

sebastienros commented 8 years ago

In summary (for myself at least) creating new content records, like new versions, concurrently, can create deadlocks, probably because multiple requests try to read and write on the same ContentItemVersionRecord table.

A solution for highly concurrent write access web apps is to use snapshot isolation.

MatteoPiovanelli-Laser commented 7 years ago

Hi. We stumbled on similar deadlock problems in one of our apps, and tried to use Snapshot isolation. That seemed to remove/reduce deadlocks. However, it caused another issue, in that it would cause duplicate entries in the ContentItemVersionRecord, all with Published=True and Latest=True.

To reproduce this:

sebastienros commented 7 years ago

Than @MatteoPiovanelli-Laser that's definitely a race, we should probably have a lock on the code that publishes a content item. And ultimately a database constraint in case this happens on a multi-node deployment.

carlwoodhouse commented 7 years ago

Agreed we've also had something similar under similar circumstances

MatteoPiovanelli-Laser commented 7 years ago

@carlwoodhouse did you find how to solve this?

carlwoodhouse commented 7 years ago

We've had it very rarely since going snapshot but we only have one person publishing at once really. So not fixed it but sebs suggestions sound solid

MatteoPiovanelli-Laser commented 7 years ago

@carlwoodhouse @sebastienros It looked fine for us too, until we tried working with a poor connection. Then we clicked a few times on either "Save" button, because the UI looked stuck as things were being processed. The result of that were 7 versions of the content with the Latest flag. Once we were aware of that issue, we tried the steps I described above and we were able to systematically have this "multiple latest" condition. As you can imagine, this is really bad, because a lot of stuff depends on the assumption that the latest version will be unique.

Suppose you have a Page with ID 103 that suffered from this issue (2 Latest entries in ContentItemVersionRecord). In another content, you select that Page in a ContentPickerField. Save. You will now see the Page twice. Save. You will now see the page 4 times. And so on.

carlwoodhouse commented 7 years ago

Agreed it's an issue :) a lock + database constraint should help though (would mean the second save/publish would error rather then break the data).

In terms of pressing the save button multiple times - wasn't this fixed from a ui point of view (js stops multiple pressing of the button, in a later version of orchard? i forget which (Of course this doesn't solve the issue in a multi-node environment (likes ours and i gather yours)) or is that only true of the publish button?

MatteoPiovanelli-Laser commented 7 years ago

I feel a db constraint for this might get messy.

Honestly, if I did not have to care about retrocompatibility or possibly massive reworks of a lot of code, I would move the "Published" and "Latest" columns to ContentItemRecord, and use them as foreign keys there, "pointing" to the corresponding records in the ContentItemVersionRecord table. That would make them automatically unique (for any ContentItem). Might have to add a db check to ensure that the ContentItemVersion record they point to has to do with their ContentItem, but that looks easier than a constraint on a subgroup of the table.

MatteoPiovanelli-Laser commented 7 years ago

Something we noticed:

I had one of those lock errors. Looking at the ContentItemVersionRecord, I noticed that I had the record for the previous version, with Published==False and Latest==False, but no new version record. I would expect the publication to happen in a single transaction, but perhaps something got in the way?