################################################################################
#   File        :   Invoke-Deadlock.ps1
#
#   Purpose     :   This script induces a deadlock in the [AdventureWorks] 
#                   database.
#
#   Parameters  :   $ServerName (System.String)
#                       The name of the SQL Server instance in which a 
#                       deadlock should be caused.
#
#   Version     :   3.0006.0010.0031 (2010-10-25)
################################################################################

Param(
    [String]$ServerName = $(Throw (New-Object -TypeName 'System.ArgumentNullException' -ArgumentList '$ServerName'))
);

#   Both connections will use the same connection string.  Note the section on 
#   "Asynchronous Processing."
[String]$ConnectionString = "Server=$($ServerName);Database=AdventureWorks;Trusted_Connection=True;Asynchronous Processing=True;Application Name=PowerShell_Invoke-Deadlock";

#   The connection for Process "A."
$Connection1 = New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $ConnectionString;

#   The connection for Process "B."
$Connection2 = New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $ConnectionString;

$Connection1.Open();
$Connection2.Open();

$Command1 = $Connection1.CreateCommand();
$Command2 = $Connection2.CreateCommand();
$Command3 = $Connection1.CreateCommand();
$Command4 = $Connection2.CreateCommand();
$Command5 = $Connection1.CreateCommand();

#   Display information on the session IDs for the two processes.
$Command1.CommandText = 'SELECT @@SPID;';
$Command2.CommandText = 'SELECT @@SPID;';
[Int]$SessionID1 = $Command1.ExecuteScalar();
[Int]$SessionID2 = $Command2.ExecuteScalar();
Write-Host "The Session ID for Process `"A`" is $($SessionID1), the Session ID for Process `"B`" is $($SessionID2)" -ForegroundColor 'Cyan';

#   Begin a transaction on Process "A" and acquire an exclusive lock on the 
#   [Purchasing].[PurchaseOrderDetail] table.
$Command1.CommandText = 'BEGIN TRANSACTION; UPDATE [Purchasing].[PurchaseOrderDetail] SET [OrderQty] = ([OrderQty] + 200) WHERE (([ProductID] = 922) AND ([PurchaseOrderID] = 499));';
[Void]$Command1.ExecuteNonQuery();

#   Begin a transaction on Process "B" and acquire an exclusive lock on the 
#   [Production].[Product] table.
$Command2.CommandText = 'BEGIN TRANSACTION; UPDATE [Production].[Product] SET [ListPrice] = ([ListPrice] * 0.9) WHERE ([ProductID] = 922);';
[Void]$Command2.ExecuteNonQuery();

$Command3.CommandText = 'UPDATE [Production].[Product] SET [ListPrice] = ([ListPrice] * 0.9) WHERE ([ProductID] = 922);';
[Void]$Command3.BeginExecuteNonQuery();

$Command4.CommandText = 'UPDATE [Purchasing].[PurchaseOrderDetail] SET [OrderQty] = ([OrderQty] - 200) WHERE (([ProductID] = 922) AND ([PurchaseOrderID] = 499));';
[Void]$Command4.BeginExecuteNonQuery();

$Command5.CommandText = 'ROLLBACK TRANSACTION;';
[Void]$Command5.ExecuteNonQuery();

#   Free the ADO.NET resources used by the script.
$Command1.Dispose();
$Command2.Dispose();
$Command3.Dispose();
$Command4.Dispose();
$Command5.Dispose();
$Connection1.Close();
$Connection1.Dispose();
$Connection2.Close();
$Connection2.Dispose();

#   Output a final status message.
Write-Host "Deadlock induced" -ForeGroundColor 'Red';
