CODEWITHSUNDEEP
transactionsrdbms
lapots
lapots

Reputation: 13415

database transaction conflict resolution

How DBMS resolve conflict in transactions and when it happens?

For example if I have a record

id  name  value
 1   N1    VAL1

And I have isolation level lower than snapshot isolation or serializable (repeatable read or read commited)

First case. I've got two transactions. First transaction T1 updates name field to N2

update table set name = 'N2' where id = '1'

Second transaction T2 updates field value to VAL2. It completes before first transaction.

update table set value = 'VAL2' where id = '1'

As far as I understand both transactions should not conflict as they change different fields of the records. Am I right? Or there will be conflict as the state of the record changed by transaction T2?

Second case. I've got two transactions. First transaction T1 updates name field to N2.

update table set name = 'N2' where id = '1'

Second transaction T2 updates name field to N3.

update table set name = 'N3' where id = '1'

And again it completes before first transaction. As far as I understand it should be a conflict as the state of name field for the first transaction is not as it was in the beginning of it. So what will happen? Transaction aborts and retry? Or it will abort and rollback?

Upvotes: 3

Views: 3227

Answers (1)

Gareth Lyons
Gareth Lyons

Reputation: 1972

You're asking how RDBMSs implement the 'I' (Isolation) part of the ACID principle. https://en.wikipedia.org/wiki/Isolation_(database_systems)

Depends on the RDBMS, but SQL Server uses locking to achieve it.
(This should also hold true for Oracle, Postgres, MySQL using InnoDB but NOT MyISAM).

The transactions are processed in the order they're received.
Due to this, and locking, in both of your examples the second transaction cannot possibly complete before the first.

1st example:

The first transaction acquires an exclusive lock on the row.
The second transaction requests an exclusive lock on the same row, but must wait.
The first transaction completes and releases its exclusive lock.
Now name = 'N2', value = 'VAL1'
The second transaction now acquires the exclusive lock on the row.
The second transaction completes and releases its lock.
Now name = 'N2', value = 'VAL2'

2nd example:

The first transaction acquires an exclusive lock on the row.
The second transaction requests an exclusive lock on the same row, but must wait.
The first transaction completes and releases its exclusive lock.
Now name = 'N2'
The second transaction now acquires the exclusive lock on the row.
Due to snapshot isolation, the second transaction is rolled back as the value of name has changed since the transaction began. Its locks are released.
Now name = 'N2'

Re-running the second transaction at this point would result in a successful update of name to 'N3' (assuming no other concurrent transactions on that row).

Upvotes: 4

Related Questions