- ozdotnet - codify.mailman3.com

Resolving a SQL server deadlock
by Tony Wright Oct. 11, 2022

Oct. 11, 2022

Hi there, I am currently trying to resolve a sql server deadlock issue. Someone in the team is adamant that the deadlock is caused by a new index we've added. I'm not sure I've ever heard of an index causing a deadlock, but I'm always interested in something new to me, anyway! I have attached a deadlock graph from SolarWinds Sql Sentry Plan Explorer and I'm hoping someone else can help me understand it. There are essentially 2 queries. (I've renamed all objects) The first query is along the lines of: SELECT DISTINCT * FROM dbo.Shipping WHERE StatusID=@StatusID AND IsLocked=@IsLocked AND TypeID=@TypeID AND ShipDate < @ShipDate. The field list is expanded and there are lots of fields. It was generated inside a third party tool. The second query is an UPDATE statement in a stored proc, along the lines of: UPDATE dbo.Shipping SET IsLocked=@IsLocked, LockedBy=CASE WHEN @Locked=1 THEN @PersonWhoLocked ELSE NULL END, LockedDate=CASE WHEN @Locked=1 THEN GetUtcDate() ELSE ModificationDate END WHERE ShippingID=@ShippingID Process 1 (126) is the SELECT, which is the deadlock victim Process 2 (133) is the UPDATE stored proc Process 3 (139) is the UPDATE stored proc [image: deadlock.png] I'm going to take a stab at what might be happening. Hopefully someone can fill the gaps in my knowledge. A deadlock occurs when Process A has a lock on Resource 1 and wants to acquire a lock on Resource 2, while Process B has a lock on Resource 2 and wants to acquire a lock on Resource 1. So reading this graph, it looks like: The 2 resources are the Page Lock on dbo.Shipping, and the Key Lock on the shipping Primary Key. So on the graph it looks to me like Process 1 holds a lock on the Page Lock resource and wants a lock on the Key Lock. Process 3 holds a lock on the Key lock resource and wants a lock on the Page Lock resource So: (1) The SELECT occurs on the shipping table and it takes a Page lock (S) (2) The first UPDATE occurs (133) and that requires an exclusive lock, but it can't get it because there's already a Shared lock, so it puts in an Intent to get an Exclusive lock (IX) (3) The second UPDATE occurs and it acquires an Exclusive lock (X) on the Primary Key Index. (4) Not sure what happens here and how this is relevant (5) The second UPDATE requests an exclusive lock on the Page of Shipping records. (6) Meanwhile the SELECT tries to get a Shared lock on one of the keys that are locked by the second UPDATE statement Can anyone help me understand what is going on here and how this is going to help me prevent further deadlocks? Kind regards, Tony

2 2

SQL server composite key column order
by Tony Wright Oct. 9, 2022

Oct. 9, 2022

Hi peeps, When setting up composite key indexes in SQL server, I have always thought that it is best to put the column that has the most variation first in the index. I've recently seen articles that claim this is a myth. So does anyone know the the facts here? Is column order important? Regards, Tony

2 1

.Net Framework 4.0
by Cameron Booth Sept. 20, 2022

Sept. 20, 2022

Hi all, I am having a problem, got issued a shiny new laptop with windows 10 (21H2). I have VS2022 installed and also VS2017 as there is still some projects on TFS (the TFS tooling seems to be better on VS2017) I have some legacy solutions running .Net Framework 4.0 that I can't open now. If I try and open the solution in either VS2022 or VS2017 I get an error saying the "Target framework not supported", giving me the option to upgrade to 4.8, download the 4.0 targeting pack or, do not load the project upgrading to 4.8 is not an option for me on these versions of the solutions. When I try to download and install the 4.0 targeting pack it tells me that .Net Framework 4 is already part of the operating system. Anybody know what it is looking for to target 4.0 framework? Anyone know how I can fool it into thinking that framework is there? Thanks in advance Cam Booth This message contains privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that you must not disseminate, copy or take any action or place any reliance on it. If you have received this message in error please notify the sender immediately via return email. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the company.

2 2

WinForms toolbox blank in .NET 6
by Greg Keogh Sept. 15, 2022

Sept. 15, 2022

Folks, my Windows Forms toolbox is empty for .NET 6 projects. I've expended hours on web searches and "Resets" and experiments but it's utterly hopeless. I've run out of ideas. The problem is only on my work PC, not my idental laptop or in a VM. Has anyone experienced this and fixed it? There must be something peculiar about my work PC, but what?!? I could start randomly uninstalling and installing stuff again, but that could go on for days with no promise of a fix. Why does crap like this happen so often? Sometimes I spend more time keeping my PC and development environment working than I do actually writing software. *Greg K*

3 4

Indexed JSON documents (file system)
by Greg Keogh Sept. 2, 2022

Sept. 2, 2022

Folks, back to work. I'm trying to convince some colleagues that they should persist "reports" from a product as JSON instead of a fiddly custom text format. Each "report" looks a lot like a self-contained document to me, with a few sections containing the parameters, layout and data of the report. I'm pitching that JSON is standard, readable by everything, change tolerant, and ... most importantly ... databases like Mongo, Cosmos, Dynamo, etc easily store and index JSON documents. They could stuff their reports into a document db and get the indexing, speed and robustness for nothing. However, some customers will want to store all the JSON reports in the file system, so I'm wondering if there is some library that indexes and manages local JSON documents. If I was younger I'd write one myself, but I'll bet someone's already done something similar. Any ideas anyone? I'm searching. I think the latest SQL Server versions do this, but it might be a bit of a heavyweight option. *Greg*

3 5

[OT] Stephen Toub's impacts
by Greg Keogh Sept. 1, 2022

Sept. 1, 2022

This is really a Friday topic, but I just found this very long interesting article by Stephen Toub titled Performance Improvements in .NET 7 <https://devblogs.microsoft.com/dotnet/performance_improvements_in_net_7/>. It's long and technical, so I plan to digest it via the tablet while relaxing in bed tonight (what else is there to do in bed at night?!) I have the same complaint as last year about Stepen's articles .. they are littered with variations of the word "impact". This article has a staggering 48 impacts in it, used in the most ludicrous and ambiguous ways. Normally he uses impact to mean "degrade" or "decrease", but as soon as the second paragraph he says "positively impact performance". So if you stick a qualifier in front of the word it can mean anything you like.. He could have just said "improves performance". Many of his impacts actually have no meaning at all in context, and many are hopelessly ambiguous, which is not welcome in a technical article by a respected software boffin. I tried to email Toub last year to politely ask him to use accurate expressions in his technical articles. I suggested he use "degrade" or "improve", or something equally expressive in place of all the impacts, but unfortunately anonymous email to him bounces. I left a similar comment after one of his posts, but it was never published. If anyone knows Stephen Toub, or knows how to contact him, please forward this message to him. We have to do something to prevent the devolution of the English language. Cheers, *Greg Keogh*

4 4

VS2022 recent list stall
by Greg Keogh Aug. 30, 2022

Aug. 30, 2022

Has anyone else got a problem with the *Open recent* list in the latest VS2022? Type some search text and it just stalls busy with empty results. [image: image.png] I've also noticed that the *Replace in Files* feature over external folders has the same bug I reported several weeks ago. It will tell you "x occurrences(s) replaced" but no files are touched. I reported this back then, but a support person noticed a duplicate posted after me, so I got marked as a duplicate (but I was first!!). I would have thought that this was a really serious issue, but it seems to have gone quiet. Doesn't anyone else use VS for global changes? It's a powerful feature. *Greg*

4 7

[OT] VS2022 Replace in Files problem
by Greg Keogh July 14, 2022

July 14, 2022

Folks, about 2 months ago, VS2022 *Replace in Files* stopped working as expected on my work PC, in VMs and on my laptop. After hours of experiments I have determined that Replace in Files will not alter any "external" files that are not part of an open project. The file types, folder locations, user account, recent minor updates, dialog options, /reset, etc make no difference. Can someone do something like this in a recent VS2022 release ... Create a C:\temp\junk.txt file and put the word FOO inside it. In VS2022 with *no projects open*, run a Replace in Files over the temp folder and file and see if you can change FOO to BAR. Does the txt file get updated? Not for me. Below is a sample of my attempt which says it made 1 change, but it's a lie. Is there some obscure option I've accidentally toggled? In VS2015 it changes files as I expect. I opened a ticket, but it's been in evaluation status for weeks -- Thanks, *Greg K* [image: image.png] [image: image.png]

2 2

Hawkeye replacement - find control and get instance name and maybe other properties
by David Burstin July 14, 2022

July 14, 2022

Hi all, Does anyone know of a current tool that can be used like Hawkeye used to be - specifically I want to be able to run a winforms app and then use the tool to target specific controls to see what their instance name is. I am fixing a bug in a legacy program and half the struggle is working out where the meatballs are in all the spaghetti. I am running VS 2022 Professional, so no access to enterprise tools. Cheers Dave

2 1

Blazor deploy error
by Greg Keogh July 13, 2022

July 13, 2022

Folks, I've been trying to deploy a Blazor app from Visual Studio 2022 for hours without success. I'm doing the usual right-click-publish that I've been doing for about a year, but yesterday it stopped working, and even this morning with a fresh mind I cannot make any progress. I've changed the pubxml options, compile options, etc. Absolutely nothing works. All I get is the generic and utterly useless error below. Normally changing an FTP or Deploy settings :wakes it up" and it works, but not this time. I'm about to delete the publish profile and make a new one, but I'm sure it's a waste of time. I'm wondering if some hosting versions have changed, or something is deprecated, something global I'm not aware of. Has anyone been through this? -- *Greg K* [image: image.png]

5 11