We have moved to GitHub Issues
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, Action
1 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()
1 source) at Umbraco.Core.Persistence.Repositories.VersionableRepositoryBase2.GetPropertyCollection(Int32 id, Guid versionId, IContentTypeComposition contentType, DateTime createDate, DateTime updateDate)
1 dtos) at Umbraco.Core.Persistence.Repositories.RepositoryBase2.GetByQuery(IQuery
1 query) at Umbraco.Core.Services.MemberService.GetByUsername(String userName) at Umbraco.Web.Security.Providers.UmbracoMembershipProvider2.GetUser(String username, Boolean userIsOnline)
at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
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.
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 ?
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 ?
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... ?)
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....
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?
Will investigate asap
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:
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.
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
Thanks Shannon :)
Will try pull when I have a gap - this may also be related to U4-5157
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 ?
Yup, that is listed in that issue
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....
Ok great, thanks! If you do get a chance would be awesome to test against latest codebase.
Cloning the repository - do I just need to pull the bin folder in to my site ?
you'll need to make sure you update to the correct branch, then run the build.bat file, then copy the bin output
Its working perfectly, not seeing all the SQL updates I was before (and its shaved around 150ms of load time from a protected page)
Are those routines also used in the getting the member for editmember.aspx in the backoffice? Seen a noticeable speed improvement there too...
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.
Thanks Shannon, great news on above :) Dare I ask about a potential release date ? :)
Should be soon! Just waiting for Sebastian to get back from holidays.
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?
@email@example.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.
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
Hey Shannon. Any news on release dates for the fixes? 😃
@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
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!
@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!
Assignee: Shannon Deminick
Backwards Compatible: True
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