How to use PowerShell to create this SQL Server blocking scenario?

Question

I can easily create a SQL Server blocking scenario using TSQL in SSMS. Run below code in SSMS tab 1.

USE tempdb;
DROP TABLE IF EXISTS SampleTable;
CREATE TABLE SampleTable (ID INT PRIMARY KEY, Name NVARCHAR(50));
INSERT INTO SampleTable (ID, Name)
VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Jim');

Run this in SSMS tab 2:

USE tempdb;
BEGIN TRANSACTION;
UPDATE dbo.SampleTable SET Name = 'Jack' WHERE ID = 2;

Run this in SSMS tab 1:

USE tempdb;
UPDATE SampleTable SET Name = 'Jack2' WHERE ID = 2;

Now I can see the last UPDATE statement got blocked. I want to mimic this behavior in PowerShell. Below is my code.

function runit($SQL, $Database = "master", $Server = ".") {
    $conn = New-Object System.Data.SqlClient.SQLConnection("Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$Database")
    $cmd = New-Object System.Data.SqlClient.SqlCommand($SQL, $conn)
    $cmd.CommandTimeout = 600 # 10 minutes
    $conn.Open()
    Write-Output "$($cmd.ExecuteNonQuery()) rows affected"
    $conn.Close()
    $results
}


$inst = "."
runit -Server $inst -SQL @"
USE tempdb;
DROP TABLE IF EXISTS SampleTable;
CREATE TABLE SampleTable (ID INT PRIMARY KEY, Name NVARCHAR(50));
INSERT INTO SampleTable (ID, Name)
VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Jim');
"@

runit -Server $inst -SQL @"
USE tempdb;
BEGIN TRANSACTION;
UPDATE dbo.SampleTable SET Name = 'Jack' WHERE ID = 2;
"@

runit -Server $inst -SQL @"
USE tempdb;
UPDATE SampleTable SET Name = 'Jack2' WHERE ID = 2;
"@

As you can see, I have a PowerShell function runit, which executes a piece of TSQL. I basically run the three same piece of TSQL. But I didn't get the blocking when I check in SSMS. Why? How to fix it?

Answer 1

The transaction is rolled back on $conn.Close(). If you run in separate powershell sessions and don't close the connection, you'll see blocking.