0

Here I have a dilemma. Let's imagine that we have a sql table like this enter image description here

It could be a problem when two or more users overwrite data in the table.

How should I check if the place hasn't been taken before update data?

I have two options

  1. in SQL query:
UPDATE ticket SET user_user_id = ? WHERE place = ? AND user_user_id is NULL
  1. or in Service layer:
try {
Ticket ticket = ticketDAO.read(place)
if (ticket.getUser() == null) {
ticket.setUser(user)
ticketDAO.update(ticket)
}else {
throw new DAOException("Place has been already tooken")
}

What way is safer and commonly used in practice?

Please, share your advice.

Volodymyr
  • 1
  • 3
  • 2
    The second version has the problem that a concurrent transaction could get in and assign a user during the time between the read and the update. – Tim Moore Jan 24 '23 at 22:18
  • It seems that anyway, ideally, you need to work with concurrent process to be able to avoid two updates at one time. Is [this another question/answer](https://stackoverflow.com/questions/22095719/what-is-a-good-approach-for-safe-concurrent-updates-in-a-relational-database) helpful for you? – rsnlpn Jan 24 '23 at 22:35

1 Answers1

1

Possible approach here is to go ahead with SQL query. After query execution check number of rows modified in ticketDAO.update method. If 0 rows modified then throw exception DAOException.

Oleksandr Myronets
  • 88
  • 8
  • Thank you for your answer. Does transaction isolation mode of repeatable read can help me in such situation? – Volodymyr Jan 25 '23 at 00:01
  • Since there is only one DB operation to perform transaction is not the right tool here. If I understood business logic correctly, you need to show some error to user in case if between the time place was selected and time order was confirmed other user already purchased ticked for this place. If so, transaction will have no effect. – Oleksandr Myronets Jan 25 '23 at 00:16
  • Solving this with a mutex assumes that all modifications are happening from the same JVM. – tgdavies Jan 25 '23 at 01:35
  • Ignore mutex related approach. I edited my answer. – Oleksandr Myronets Jan 25 '23 at 02:37
  • @OleksandrMyronets thanks for your advices! It works. Now I'm just learning to write code and it's my first web application. Could you advice me how usually devs solve this problem? Maybe there are solutions in Spring and Hibernate frameworks, but due the conditions of my project I cant't use ORM frameworks. It's just MVC-app with ConnectionPool, DAO layer, Service layer, Controllers and view. So I'm interesting how devs really solve this problem in real projects based on servlets – Volodymyr Jan 25 '23 at 12:07
  • @Volodymyr I believe that this question goes far beyond the original question and deserve separate topic rather than discussion in comments. For more fundamental study on subject I suggest reading about [CAP theorem](https://en.wikipedia.org/wiki/CAP_theorem) – Oleksandr Myronets Jan 25 '23 at 20:20