1

I need help with understanding resultant behaviour of concurrent transactions updating same row of the database.

Database : MySQL

Scenario:

1) Transaction A will update row (1,2,3)
2) Transaction B will update row (1,3,4)

Since both transactions are executing in parallel, in auto-commit mode turned off, would the final state (for row 1 and 3) will reflect the changes made by both A and B or there is a potential for data loss.

Also would the answer change if the transactions are simply incrementing the data records?

d-_-b
  • 21,536
  • 40
  • 150
  • 256
user1855193
  • 123
  • 3
  • 10
  • Are you using MyISAM or InnoDB in your MySQL database? – Tim Biegeleisen Sep 09 '15 at 07:59
  • I haven't yet decided. Is there one that I shall prefer over the other? I want to be able to have multiple threads update multiple rows as mentioned in the case above. @TimBiegeleisen – user1855193 Sep 09 '15 at 08:33
  • 1
    According to [this SO post](http://stackoverflow.com/questions/1195858/how-to-deal-with-concurrent-updates-in-databases), which is admittedly a few years old, MyISAM does not support transactions, but InnoDB does. – Tim Biegeleisen Sep 09 '15 at 08:34

1 Answers1

1

If both transactions update the same row, the second transaction will be blocked until the first transaction is either rolled back or committed. In other words the transactions will not run in parallel at the end.

This actually depends on isolation level, see for example http://developer.mimer.com/documentation/latest_jdbcguide_html/programming.html for details.

Zbynek Vyskovsky - kvr000
  • 18,186
  • 3
  • 35
  • 43
  • Thanks @Zbynek. Quick followup question. If I use the transaction isolation level serializable, in that case can I cause a db deadlock if Tx A updates (1,2,3) and Tx B updates (2,1,4)? – user1855193 Sep 09 '15 at 08:47
  • Deadlock in general is caused by locking (at least) two resources in opposite order (in the simplest case). E.g. when TxA updates row1, TxB updates row2 and then TxA tries updating row2 and TxB tries updating row1. So if either of the transaction updates just single row and commits, it couldn't cause deadlock. – Zbynek Vyskovsky - kvr000 Sep 09 '15 at 12:30
  • Thanks @Zbneyk. However, I am using the auto-commit mode turned off. So in case two transactions update two resources in different order would that have a possibility of a deadlock? I think yes, but just want to double check. – user1855193 Sep 09 '15 at 21:23
  • If you're using auto-commit turned off there will be possibility of a deadlock as this holds the locks until the transaction is committed. If you set auto commit to on, the transaction will be automatically committed after each statement so it won't hold any locks and therefore no deadlock could happen. – Zbynek Vyskovsky - kvr000 Sep 10 '15 at 09:58
  • I have one question in my code I am using server and client session obj. like this `Session clientSession=HibernateUtilLocal.getSession(); Session serverSession=HibernateUtilServer.getSession();' serverSession.beginTransaction().commit(); clientSession.beginTransaction().commit(); But problem is that if I am committing server session data and after that line getting Network exception it means client session data is not committing. So I want to rollback both session data. How to do this ? currently only I am getting only rollback in client session – Coder Mar 21 '18 at 05:26