• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 Concurrency issue

 
Giovanni Siciliano
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DB: DB2 V7

I am having a concurrency issue.

The web-system that I am building is for administrators. They will have the ability to change associate information. I will have a table that stores this information. The problem I am running into is:

If two admins were logged in and they were making changes to the same employee. The first admin is just changing the last name. The second admin is just changing the address.

If the first admin submits its changes before the second admin, the address would revert back to its old state.

What is the best way to handle this?

Note: We don't want to lock out any of the admins from making associate changes, for instance if the first admin is making changes to Jenna Smith, we wouldn't want to block any other admin from making changes to her as well.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Giovanni,
Two approaches that come to mind:
1) Add a last updated field to the row. If the 2nd admin sees the row was updated, he needs to make the update again. This way there isn't a lock, but there are no concurrent updates.
2) When the admin starts to update a row, get all the data. See which fields were updated and only write those back to the database. This way allows updates to different fields, but still can allow the same field to be overlaid.
 
Giovanni Siciliano
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply Jeanne.

These are definitely helpful solutions that I will consider. Another quick question, what would be the best isolation level to use in this scenario?

Thanks again!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Giovanni,
The isolation level is for an individual database transaction. Since the edits are occuring across database transactions, you can use a lower one (like repeatable read.) Serializable wouldn't buy you anything extra because you aren't holding a lock on the row while the admins edit.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's worth bearing in mind that any discussion on isolation levels must be done in the context of the DB driver. For instance, the WebLogic jDriver for Oracle supports only read committed and serializable.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic