Hi db042190,
Pessimistic locking: Records are locked when they are read within a transaction, preventing any user from making any changes before the transaction is completed. This ensures that updates succeed, unless a deadlock occurs. This is the default locking method used by MS SQL Server 2000.
Optimistic locking: A method which records are NOT locked when they are read within a transaction. This allows users to make changes to the database. The records are locked only when they are updated. This introduces the possibility that two transactions modify data at the same time. Although this is not the default locking method used by MS SQL Server 2012, it can be easily implemented by using cursors. This is because a cursor allows you to control an individual record in a recordset. Cursors are covered in a prior module in this course.
Deadlock: A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. This contention issue is terminated by the SQL Server intervention. It chooses a victim from the transactions who are involved in the deadlock, forces it to rollback all actions.
Agree with Erland. Set the deadlock priority is a great method. Here is a related article for your reference.
https://blog.quest.com/the-anatomy-of-sql-server-deadlocks-and-the-best-ways-to-avoid-them/
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".