locking – Why did SQL Server acquire a page lock when I accessed a record by its primary key?

We have a DML that’s updating a SQL Server table:

UPDATE mytable
SET
   Column1 = @Column1,
   Column2 = @Column2,
   Column3 = @Column3,
   Column4 = @Column4,
   Column5 = @Column5,
   Column6 = @Column6,
   Column7 = @Column7,
   Column8 = @Column8,
   Column9 = @Column9,
   Column10 = @Column10
WHERE RowID = @RowID

We have multiple indexes on this table. Column7 has a non-clustered index. Then, we have multi-column non-clustered indexes between Column1 and Column7, Column2 and Column5, Column2 and Column6. Finally Column6 and Column7 has a unique index. This table has millions of records.

Recently, there was a deadlock situation where two sprocs were hitting this table simultaneously. It looked like each had acquired a page level lock. The above DML is from the second proc.

I was under the impression that, since we are accessing a record by its PK (which is the RowID), only a row level lock would have been acquired. So, my question is, why did SQL Server decide to acquire a page lock? Is it because of the indexes?

I’m thinking of using WITH (ROWLOCK) hint in this query, but I’m not sure if it will fix the issue. If SQL decides to acquire tons of row locks, it might escalate to a table lock (which will be worse than the page lock).

How securely should one treat their primary public key?

First of all, is my understanding correct that primary keys and subkeys are all key pairs consisting of a private and a public key?

I get the rationale behind using primary keys and subkeys1, and why one should keep the private primary key (or master key) “very, very safe“, but not sure what the consensus is on handling the public primary key? Is this the one that should be shared with others or uploaded to a key server2? (Sometimes this may be an explicit requirement.)


It was also confusing to see sentences such as “GnuPG actually uses a signing-only key as the master key” but realized that this refers to the private primary key because (quoting from the same source)

You use the private key to digitally sign files, and others use the public key to verify the signature. Or, others use the public key to encrypt something, and you use the private key to decrypt it.


(1):

  • To use different keys for signing and encrypting
  • to mitigate the effect of lost/stolen keys (see also)
  • (TODO: continue list with more)

(2): Superuser thread: Where to upload PGP public key? Are KeyServers still surviving?

sql server – handling primary key with large tree, best way to get root id from child table X

I always used database design using this pattern;

root table with PK id1 
child1 table with PK id2 FK id1
child2 table with PK id3 FK id2
child3 table with PK id4 FK id3
etc...

so if i have to get id1 from child5 table I need to traverse all child tables up to the root one (multiple join)

ex;

select root.id
from root
   inner join child1 ...
   inner join child2 ...
   inner join child3 ...
   inner join child4 ...
   inner join child5 ...
where child5.id = X

is that “ok” as a design or there is way to optimize it?

I could see a few way to do it;

  1. bring the idX as a FK in the table that I expect, so I could modified (add the proper FK) down the line to any childX table as needed
  2. just add the FK as I create new table, this seem wasteful, ex; child5 table would have at mininum 4 FK (id1, id2, id3, id4)

with these options i could simply do;

select child5.rootid
from child5
where child5.id = X

questions

  1. is any of my option above is a good thing to do or there is a better way of doing this?
  2. is there any name given to this pattern (so i can search online, i can’t think of any)

gui design – What should be the vertical placement of primary button on a mobile screen? And why?

enter image description hereA user scans the screen from left to right and top to bottom.
So, we use the primary action (or primary CTA) on the right side while designing for the web.
If we consider top to bottom scan approach in mobile, then why do we use the primary action above the secondary action while designing for mobile?

multiple monitors – Change primary mode in xrandr, and delete invalid mode

When I switch to this console, or login, I have to run xrandr:

celiyah@celiyah-pc:~/Code/artifactory/terraform$ xrandr
Screen 0: minimum 320 x 200, current 4880 x 2560, maximum 16384 x 16384
DP-1 connected primary 3440x1440+0+562 (normal left inverted right x axis y axis) 800mm x 335mm
   3440x1440     59.97 +  49.99    29.99  
   2560x1080     60.00  
   1920x1080     60.00    60.00    50.00    59.94  
(...)
   3440x1440_30  29.95* 
HDMI-2 connected 1440x2560+3440+0 left (normal left inverted right x axis y axis) 609mm x 349mm
   2048x1080     24.00  
   1920x1080     60.00    60.00    50.00    59.94    30.00    25.00    24.00    29.97    23.98  
   1920x1080i    60.00    50.00    59.94    50.00  
(...)
   2560x1440_60.00  59.96  
   2560x1440_30.00  29.94* 

…otherwise it chooses that first mode, 3440×1440@60Hz, which doesn’t work for my monitor and results in a lot of churn on the device.

I want to eliminate that mode, but I can’t:

celiyah@celiyah-pc:~/Code/artifactory/terraform$ sudo xrandr --delmode DP-1 3440x1440
X Error of failed request:  BadAccess (attempt to access private resource denied)
  Major opcode of failed request:  140 (RANDR)
  Minor opcode of failed request:  19 (RRDeleteOutputMode)
  Serial number of failed request:  38
  Current serial number in output stream:  39

(same thing happens without sudo)…

How can I prevent it from trying to do this to my poor monitor?

sql server – partitioned table – replication reverted to primary filegroup, can i re-link to the partitions?

So we have 2 servers

Server A. production (no partitions)

Server B. New Server (partitions and compression)

we restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy

Trying to be clever we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.

We cannot query any old data on the new server, only data from when we turned on replication
The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, Its using the PRIMARY filegroup (same as the current live server is using)

On the physical disk I can see the partition files, and they are still massive with GB’s of data, so i know the data is there, but it appears the table has lost the link to it

It appears turning on replication replicated the filegroup side of things, is there a way to point the table back to partitions and all the work we did getting it ready for switch over

Its a pretty large database, this one table is 11TB on the production server.

Sql Server 2017 enterprise.

categories – WooCommerce products sorting by primary category first

I have a site with woocommerce and yoast seo plugin. I want to list the products on category archive page, by making the products of primary category (from yoast plugin) come first. Currently it does not do that. It is listing the products randomly or by menu order. The products have multiple categories assigned. For ex. the page of ‘Fashion’ category archive should list the products with primary category ‘Fashion’ to come first.
Thanks

c# – How to just pass in primary key values to my CRUD functions?

Here is my code:

albums.cshtml

@page
@model Project.Pages.AlbumsModel            
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<div class="row">
    <h1 class="display-2">Albums</h1>
    <table class="table">
        <thead class="thead-inverse">
            <tr>
                <th>Album ID, artist ID, Album title</th>
            </tr>
        </thead>
        <tbody>
            @foreach (string album in Model.Albums)
            {
                <tr>
                    <td>@album</td>
                    <td><a asp-page="/Albums/Delete" asp-route-id="@album">Delete</a></td>
                    <td><a asp-page="/Albums/Edit" asp-route-id="@album">Edit title</a></td>
                </tr>
            }
        </tbody>
    </table>
</div>
<div class="row">
    <p>Enter a title & ArtistID for a new album:&nbsp;</p>
    <form method="POST">
        <div><input asp-for="Album.Title" /></div>
        <div><input asp-for="Album.ArtistId" /></div>
        <input type="submit" />
    </form>
</div>

albums.cshtml.cs

using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Project.Models;
using System;

namespace Project.Pages
{
    public class AlbumsModel : PageModel
    {
        private Chinook db;

        public AlbumsModel(Chinook injectedContext)
        {
            db = injectedContext;
        }
        public IEnumerable<string> Albums { get; set; }
        public void OnGet()
        {
            ViewData("Title") = "Chinook Web Site - Albums";
            Albums = db.Albums.Select(s => s.AlbumId.ToString() + ". " + s.ArtistId + ". " + s.Title);
        }
        (BindProperty)
        public Album Album { get; set; }

        public IActionResult OnPost()
        {
            if (ModelState.IsValid)
            {
                db.Albums.Add(Album);
                db.SaveChanges();
                return RedirectToPage("/albums");
            }
            return Page();
        }

        public IActionResult DeleteAlbum(int AlbumId)
        {
            var album = db.Albums.Find(AlbumId);

            if (album == null) return Page();

            db.Albums.Remove(album); db.SaveChanges(); return RedirectToPage("/albums");
        }
    }
}

delete.cshtml

@page  "{id?}"
@model Project.Pages.Albums.DeleteModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

@{
    ViewData("Title") = "Delete";
}

<h1>Delete</h1>

<p class="text-danger">@Model.ErrorMessage</p>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Album</h4>
    <hr />
    <dl class="row">
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Album.Title)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Album.Title)
        </dd>
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Album.ArtistId)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Album.ArtistId)
        </dd>
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Album.AlbumId)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Album.AlbumId)
        </dd>
    </dl>

        <form method="post">
        <input type="submit" value="Delete" asp-route-id="@Model.Album.AlbumId" class="btn btn-danger" /> |
        <a asp-page="/Albums">Back to List</a>
    </form>
</div>

delete.cshtml.cs

#region snippet_All
using Project.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

namespace Project.Pages.Albums
{
    public class DeleteModel : PageModel
    {
        private readonly Chinook _context;

        public DeleteModel(Chinook context)
        {
            _context = context;
        }

        (BindProperty)
        public Album Album { get; set; }
        public string ErrorMessage { get; set; }

        public async Task<IActionResult> OnGetAsync(string? id, bool? saveChangesError = false) //changed int -> string
        {
            if (id == null)
            {
                return NotFound();
            }
            //using string.split method to split the id parameter, and get the albumid.
            var albumid = System.Convert.ToInt32(id.ToString().Split('.')(0));

            Album = await _context.Albums
                .AsNoTracking()
                .FirstOrDefaultAsync(m => m.AlbumId == albumid);

            if (Album == null)
            {
                return NotFound();
            }

            if (saveChangesError.GetValueOrDefault())
            {
                ErrorMessage = "Delete failed. Try again";
            }

            return Page();
        }

        public async Task<IActionResult> OnPostAsync(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var album = await _context.Albums.FindAsync(id);

            if (album == null)
            {
                return NotFound();
            }

            try
            {
                _context.Albums.Remove(album);
                await _context.SaveChangesAsync();
                return RedirectToPage("/Albums");
            }
            catch (DbUpdateException /* ex */)
            {
                //Log the error (uncomment ex variable name and write a log.)
                return RedirectToAction("/Albums/Delete",
                                     new { id, saveChangesError = true });
            }
        }
    }
}
#endregion

edit.cshtml

@page
@model Project.Pages.Albums.EditModel

@{
    ViewData("Title") = "Edit";
}

<h1>Edit</h1>

<h4>Album</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form method="post">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="Album.AlbumId" />
            <div class="form-group">
                <label asp-for="Album.Title" class="control-label"></label>
                <input asp-for="Album.Title" class="form-control" />
                <span asp-validation-for="Album.Title" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Save" class="btn btn-primary" />
            </div>
        </form>
    </div>
</div>

<div>
    <a asp-page="/Albums">Back to List</a>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

edit.cshtml.cs

using Project.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;

namespace Project.Pages.Albums
{
    public class EditModel : PageModel
    {
        private readonly Chinook _context;

        public EditModel(Chinook context)
        {
            _context = context;
        }

        (BindProperty)
        public Album Album { get; set; }
        public string ErrorMessage { get; set; }

        #region snippet_OnGetPost
        // public async Task<IActionResult> OnGetAsync(int? id)
        // {
        //     if (id == null)
        //     {
        //         return NotFound();
        //     }

        //     Album = await _context.Albums.FindAsync(id);

        //     if (Album == null)
        //     {
        //         return NotFound();
        //     }
        //     return Page();
        // }

        public async Task<IActionResult> OnGetAsync(string? id, bool? saveChangesError = false) //changed int -> string
        {
            if (id == null)
            {
                return NotFound();
            }
            //using string.split method to split the id parameter, and get the albumid.
            var albumid = System.Convert.ToInt32(id.ToString().Split('.')(0));

            Album = await _context.Albums
                .AsNoTracking()
                .FirstOrDefaultAsync(m => m.AlbumId == albumid);

            if (Album == null)
            {
                return NotFound();
            }

            if (saveChangesError.GetValueOrDefault())
            {
                ErrorMessage = "Delete failed. Try again";
            }

            return Page();
        }

        public async Task<IActionResult> OnPostAsync(int id)
        {
            var albumToUpdate = await _context.Albums.FindAsync(id);

            if (albumToUpdate == null)
            {
                return NotFound();
            }

            if (await TryUpdateModelAsync<Album>(
                albumToUpdate,
                "album",
                s => s.Title))
            {
                await _context.SaveChangesAsync();
                return RedirectToPage("/Albums");
            }

            return Page();
        }
        #endregion

        private bool AlbumExists(int id)
        {
            return _context.Albums.Any(e => e.AlbumId == id);
        }
    }
}

My code build and runs OK, but when I go to edit, I get, for instance

This localhost page can’t be found
No web page was found for the web address: https://localhost:5001/Albums/Edit/353.%20123.%20Test

I’m sure it should just be passing in the AlbumId, as in

https://localhost:5001/Albums/Edit/353

…and when I manually change the URL to that, it works.

The delete function works from, for instance…

https://localhost:5001/Albums/Delete/353.%20123.%20Test2

…where ‘353’ is the AlbumId, ‘123’ is the ArtistId, and ‘Test2’ is the Title.

…but that actually does work. So how can I just get both Delete and Edit to work from the AlbumId? That is all that’s needed, right? TIA for any help.