update conflicts with concurrent update

In this article, we will discuss the overview of transaction, deadlock and will focus on update conflicts with the concurrent update and understand with the help of an example. Let’s discuss it one by one.

Transaction :
A transaction is a logical unit of work that can have one or more SQL statements.  A transaction ends when either you commit it or roll back it completely.

Deadlock :
A deadlock occurs when exclusive locks are held on resources required by multiple processes and those processes cannot continue to completion.

Deadlock and update conflicts with the concurrent update :
A “deadlock update conflicts with concurrent update” exception occur when multiple transactions want to modify the same row at the same time (or different time before commit by the other). Only one updater can really change the row and commit. As long as the first transaction hasn’t committed, the update in the second /other transactions will wait (indefinitely or until the configured timeout). As soon as the first transaction commits, the update in the second transaction will end with this error NOTE: If instead the first transaction had rolled back, the second would have continued because the update done by transaction1 was rolled back.

Avoid Deadlock :
To avoid deadlock, i.e., to avoid the lock to be held on a resource (Row here) by multiple processes, firebird raises this kind of exception. Now let us look at an example to clearly understand this kind of problem as follows.

Example –
Here, we will understand the concept with the help of an example. 

• Suppose that we have already created a table name “Emp” In the database ‘Test1.fdb’ which keeps the ID of the employees & the Name of the Employees. The table data is as follows.

ID	NAME
100	Rita
20	John
3	Albert

• Lets us assume we have 2 transactions: Trans1 & Trans2. Trans1 started at time t1 & Trans2 started at time t2. Now both wanted to do update the same row, row having name = ‘John’. 
• Transaction Trans1 started the update at t3 time. Before Trans1 commits, Transaction Trans2 tries to update the same row which was updated by Trans1. So Trans2 goes in waiting for the state, waiting for Trans1 to either commit or rollback.
• See the timeline figure to understand it more clearly as follows.

Time	Trans1	Trans2
t1	Start
t2		Start
t3	Update row having name = John
t4		Update row having name = John
t5	Commit	Update conflict

Note :

• If Trans1 commit — Trans2 comes out of wait state & error message — “deadlock; update conflicts with concurrent update” will be displayed.
• If Trans1 ROLLBACK — Trans2 comes out of wait state & can complete the update on the same row.
• To check this, open 2 firebird ISQL Tool windows & run Trans1 on 1 window & Trans2 on the other.

• In the above window, in Trans1, you have not committed yet.
• After you commit the Trans1, the Error message – “deadlock; update conflicts with concurrent update” will be displayed in the Trans2 window.

To avoid this kind of error message :
Either do rollback in transaction Trans1 or Start the update on the same row in Trans2 only when Trsan1 commits.