Database

How to optimize Hash Match aggregation in SQL Server execution plan for the below

------------------------------------------------------Transaction 1------------------------------------------------------
Transactionname="DELETE"
waitresource="KEY: 6:72057594046054400 (6a6c767d8e74)"
lasttranstarted="2020-07-11T05:48:38.007"
lockMode="U"
isolationlevel="read committed (2)" 
status="suspended"

DELETE FROM [Channel] WHERE FileInventoryKey IN (SELECT FileInventoryKey FROM #FileInventoryKeys)


--------------------------------------------------------Transaction 2------------------------------------------------------
Transactionname="UPDATE"
waitresource="KEY: 6:72057594046054400 (93201dea6b78)"
lasttranstarted="2020-07-11T05:48:38.007"
lockMode="U"
isolationlevel="read committed (2)"
status="suspended"

update ch    set ChannelKey = c.ChannelKey    
from Channel ch    
join ChannelVersion c on c.ChannelID = ch.ChannelID    
join Channel cc on cc.ChannelKey = c.ChannelKey        and (cc.HasIntervalData = 0 and ch.IsRegister = 'true') --register channels      where c.ChannelVersionKey in (select ChannelVersionKey from [#UpdatedChannels]

CREATE TRIGGER AfterInsert
   ON  InspectionItems
   AFTER INSERT
AS 
BEGIN
	declare @InspectionItemNumber nvarchar(20), @InspectionItemId int

	select @InspectionItemId = ins.InspectionItemId FROM INSERTED ins;

	update InspectionItems set InspectionItemNumber = 'T' + RIGHT('00000'+CAST(InspectionItemId AS VARCHAR(5)),6)
	where InspectionItemId = @InspectionItemId
END
GO

HI Team,

I am facing deadlock issue in the SQL Server. I have two process using the same table at same time. first process will insert the records using bulk insert and second process will update the records in the same table.

Please could you let me know how can avoid deadlock.

<?php
// Include config file
require_once "config.php";
 
// Define variables and initialize with empty values
$email = $password = $confirm_password = "";
$username_err = $username = $confirm_username_err = "";
$email_err = $password_err = $confirm_password_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Validate username
    if(empty(trim($_POST["email"]))){
        $email_err = "Please enter your email.";
    } else{
        // Prepare a select statement
        $sql = "SELECT id FROM users WHERE email = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "s", $param_email);
            
            // Set parameters
            $param_email = trim($_POST["email"]);
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                /* store result */
                mysqli_stmt_store_result($stmt);
                
                if(mysqli_stmt_num_rows($stmt) == 1){
                    $email_err = "This email is already registered.";
                } else{
                    $email = trim($_POST["email"]);
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
	 }
    
    // Validate username
    if(empty(trim($_POST["username"]))){
        $username_err = "Please enter a username.";     
    } elseif(strlen(trim($_POST["username"])) < 6){
        $username_err = "username must have atleast 6 characters.";
    } else{
        $username = trim($_POST["username"]);
    }	
    }
    
    // Validate password
    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter a password.";     
    } elseif(strlen(trim($_POST["password"])) < 6){
        $password_err = "Password must have atleast 6 characters.";
    } else{
        $password = trim($_POST["password"]);
    }
    
    // Validate confirm password
    if(empty(trim($_POST["confirm_password"]))){
        $confirm_password_err = "Please confirm password.";     
    } else{
        $confirm_password = trim($_POST["confirm_password"]);
        if(empty($password_err) && ($password != $confirm_password)){
            $confirm_password_err = "Password did not match.";
        }
    }
    
    // Check input errors before inserting in database
    if(empty($email_err) && empty($password_err) && empty($confirm_password_err)){
        
        // Prepare an insert statement
        $sql = "INSERT INTO users (email, username, password) VALUES (?, ?)";
         
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "ss", $param_email,$param_username $param_password);
            
            // Set parameters
            $param_email = $email;
			$param_username = $username
            $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Redirect to login page
                header("location: login.php");
            } else{
                echo "Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Close connection
    mysqli_close($link);
}
?>
 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Sign Up</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        body{ font: 14px sans-serif; }
        .wrapper{ width: 350px; padding: 20px; }
    </style>
</head>
<body>
    <div class="wrapper">
        <h2>Sign Up</h2>
        <p>Please fill this form to create an account.</p>
        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <div class="form-group <?php echo (!empty($email_err)) ? 'has-error' : ''; ?>">
                <label>Email</label>
                <input type="text" name="email" class="form-control" value="<?php echo $email; ?>">
                <?php echo $email_err; ?>
            </div>
			<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>">
                <label>Username</label>
                <input type="text" name="username" class="form-control" value="<?php echo $username; ?>">
                <?php echo $username_err; ?>
            </div> 
            <div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>">
                <label>Password</label>
                <input type="password" name="password" class="form-control" value="<?php echo $password; ?>">
                <?php echo $password_err; ?>
            </div>
            <div class="form-group <?php echo (!empty($confirm_password_err)) ? 'has-error' : ''; ?>">
                <label>Confirm Password</label>
                <input type="password" name="confirm_password" class="form-control" value="<?php echo $confirm_password; ?>">
                <?php echo $confirm_password_err; ?>
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-primary" value="Submit">
                <input type="reset" class="btn btn-default" value="Reset">
            </div>
            <p>Already have an account? <a href="login.php">Login here</a>.</p>
        </form>
    </div>    
</body>
</html>