Deadlock in transaction in SQL Server

Deadlock is occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward.

  

Here transaction A locks the table 1 and the Transaction lock the Table 2 and Transaction A is waiting for the table 2 and transaction B is waiting for the table 2. Here Deadlock occurs.

Let’s see the example

In this example I am creating two transactions A and B.

  

Now I am executing the both Transaction in Transaction A I am executing EMP update and Transaction B executing EMP1 and for both transaction i am not committing the transaction.

  

Both executed and updated the records on both table. Keep in mind for both transaction are open ( not committed) . Now both table EMP and EMP1 both are locked. Now For Transaction A I am running the second update statement.

  

See still it is executing.

Now I am executing the update statement for the Transaction B

  

One row affected. But see the Transaction A

Throwing error: Transaction was deadlocked on the lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.