U4-5069 - Deadlock occurring in MemberService.GetByUsername

Created by Bradley Kronson 09 Jun 2014, 09:47:34 Updated by Sebastiaan Janssen 26 Sep 2017, 10:56:13

Relates to: U4-5157

After upgrading to 6.2.1 I'm seeing an intermittent deadlock error when calling GetByUsername

Have a feeling this is also causing the server load and SQL Server memory usage to spike. Site becomes unresponsive for quite a while after this and then settles.

Not sure if this is related to U4-4931 ?

An unhandled exception occurred System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 59) 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 Umbraco.Core.Persistence.Database.d__1c1.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Umbraco.Core.Persistence.Repositories.VersionableRepositoryBase2.GetPropertyCollection(Int32 id, Guid versionId, IContentTypeComposition contentType, DateTime createDate, DateTime updateDate) at Umbraco.Core.Persistence.Repositories.MemberRepository.BuildFromDtos(List1 dtos) at Umbraco.Core.Persistence.Repositories.RepositoryBase2.GetByQuery(IQuery1 query) at Umbraco.Core.Services.MemberService.GetByUsername(String userName) at Umbraco.Web.Security.Providers.UmbracoMembershipProvider2.GetUser(String username, Boolean userIsOnline) at Umbraco.Web.Routing.PublishedContentRequestEngine.EnsurePublishedContentAccess() at Umbraco.Web.Routing.PublishedContentRequestEngine.HandlePublishedContent() at Umbraco.Web.Routing.PublishedContentRequestEngine.FindPublishedContentAndTemplate() at Umbraco.Web.Routing.PublishedContentRequestEngine.PrepareRequest() at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) ClientConnectionId:23204414-36fe-483c-8b5c-5c5b5349a398


Shannon Deminick 22 Jun 2014, 23:58:20

Now that hardest part is how do we replicate this...

The underlying code in this method is very simple and the same type of code is being used in many areas of the services/repository layer. We'll need to investigate this more but if you have any more information on ways to replicate this please add them here.

Bradley Kronson 23 Jun 2014, 06:55:24

Thanks Shannon, will have a look and see if I can come up with a case that is repeatable - I could provide you with site source code/db if it helps ?

Bradley Kronson 23 Jun 2014, 17:28:54

Interesting article (plus discussion underneath) by Jeff Atwood, not sure if using nolocks on pure SQL reads like in this could would be relevant or possible - seems it what is used on Stack Overflow ?


Shannon Deminick 24 Jun 2014, 05:36:13

Hi, if you can reliably reproduce this error then your codebase would help but if you can't then we're stuck until we can reproduce it.

What SQL server are you using?

Does this happen at a particulate point in time (i.e. during startup, when loading a certain page, etc... ?)

Bradley Kronson 30 Jun 2014, 11:49:13

SQL Server 2008, can't point to anything in particular, its pretty random - I'm trying to look at it some more....

Tell me, does membership.getuser save a user every single time it gets called? I'm looking at data in the mini-profiler and I am seeing a LOT of updates to the currently logged in user via SQL calls....

Bradley Kronson 30 Jun 2014, 14:11:58

Is there any reason the HasAccess routines called GetUser with userIsOnline set to true - surely this updates the member and fires a save each time it is called - what happens with this function is called multiple times for all the children of a specific document?

Shannon Deminick 01 Jul 2014, 00:19:07

Will investigate asap

Shannon Deminick 01 Jul 2014, 02:23:33

Thanks for finding this, the GetCurrentUser() was indeed setting active to true and making an SQL write statement. I have refactored this so that GetCurrentUser() does not set active to true and created another method: GetCurrentUserOnline which does - this now only get's used when querying a members login status in our membership helper controllers

I've committed this in v6/7:

rev: 7a12060c37e958cb9c3a16a7c99bc619c36e931c

The 'HasAccess' calls are slightly different and that code has been the way it is for a very long time but I've changed it now for the same reason, it shouldn't be writing to sql when checking access.

rev: 68b17bff08ce4462e56247f0a001201f0f714004

Also note that the public access stuff is old legacy code and needs to be rewritten/migrated: http://issues.umbraco.org/issue/U4-4216

Is there any chance you can test with the latest codebase?

I'll try to get a nightly build out for this too but in the meantime, you'll have to build from the 6.2.2 branch on github

Bradley Kronson 01 Jul 2014, 04:32:01

Thanks Shannon :)

Will try pull when I have a gap - this may also be related to U4-5157

Bradley Kronson 01 Jul 2014, 04:49:46

Sorry @Shandem, I know this is probably the inappropriate place to be asking this, but since you mentioned the refactoring/migration of the public access code above, I was wondering, has any thought been given to moving the storage of the public access data to a SQL table from the XML config file ? Surely there are huge speed improvements to be gained from doing this ?

Shannon Deminick 01 Jul 2014, 04:52:44

Yup, that is listed in that issue

Bradley Kronson 01 Jul 2014, 08:32:22

PS I have cached the member in the Session variable after first look up and my SQL deadlocks have disappeared (so far), so quite sure your fix will resolve it....

Shannon Deminick 02 Jul 2014, 01:09:06

Ok great, thanks! If you do get a chance would be awesome to test against latest codebase.

Bradley Kronson 02 Jul 2014, 06:32:54

Cloning the repository - do I just need to pull the bin folder in to my site ?

Shannon Deminick 02 Jul 2014, 06:34:57

you'll need to make sure you update to the correct branch, then run the build.bat file, then copy the bin output

Bradley Kronson 02 Jul 2014, 11:09:23

Its working perfectly, not seeing all the SQL updates I was before (and its shaved around 150ms of load time from a protected page)

Bradley Kronson 02 Jul 2014, 11:26:27

Are those routines also used in the getting the member for editmember.aspx in the backoffice? Seen a noticeable speed improvement there too...

Shannon Deminick 03 Jul 2014, 02:14:27

The stuff I changed specifically for this shouldn't really have much affect there, however there's been a bunch of bug fixes in the latest branch so might be the result of a few changes/fixes.

This is great news though! Thanks so much for your patience and help!

Also note that I'm working on a custom branch atm regarding performance at the repository layer and hopefully that can be merged into 7.1.5 (and 6.2.2) before they are released - should have significant performance improvements in certain areas.

Bradley Kronson 03 Jul 2014, 06:46:36

Thanks Shannon, great news on above :) Dare I ask about a potential release date ? :)

Shannon Deminick 03 Jul 2014, 06:50:27

Should be soon! Just waiting for Sebastian to get back from holidays.

Chris Lord 24 Jul 2014, 10:54:52

I hope 7.1.5 final release is coming soon. I am building a site that uses access controlled content and I need to use HasAccess when building the navigation. I am seeing a member update for every item in the nav (assume as it checks access).

I am concerned on the impact when this goes into production with over 100 users using the site! Is there a workaround, as this could be a real problem for me?

Christopher Waters 19 Aug 2014, 21:19:25

@lordy1981@gmail.com Did you ever figure out a workaround to the HasAccess issue? The site I'm working on also has navigation that depends on it and I've been running into the same deadlocking issue.

Bradley Kronson 20 Aug 2014, 06:09:00

I created a lookup table that stores the groups that can access each piece of content. Kind of like a SQL mirror of the access.config file. Used the access change events to update it. Quite painful

Bradley Kronson 20 Aug 2014, 06:09:36

Hey Shannon. Any news on release dates for the fixes? 😃

Chris Van Oort 20 Aug 2014, 14:59:23

@Christopher.Waters and I just tested the 7.1.5 nightly build and are still experiencing the same deadlocking issue via umbraco.library.IsProtected and umbraco.library.HasAccess

Ricardo Marcelino 26 Sep 2017, 10:35:32

Hello, experiencing this issue in version 7.5.9. Different users are being locked (disable umbraco access). The only thing i can identify is that the User Type associated isn't an Administrator, but an Editor!

Sebastiaan Janssen 26 Sep 2017, 10:56:13

@rmrmarcelino That has nothing to do with a deadlock in the database. I think it would be best if you create a thread on the forum with your exact scenario. So what exactly are you trying to do, what do you see happening exactly, what did you expect to happen instead? The community at https://our.umbraco.org will try to help you as best as they can!

Priority: Major

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.1.0, 6.2.0, 7.1.1, 7.1.2, 7.1.3, 6.2.1, 7.1.4

Due in version: 7.1.5, 6.2.2


Story Points: