0

I have a database DB with table T (id int, version timestamp) and application APP that works with this database and table. What I need to do:

  1. Lock table T.
  2. Select some rows from table T.
  3. Get current time curTimeStamp.
  4. Unlock table T.
  5. All rows thar are updated after unlocking tables (#4) must have version > curTimeStamp. Their version is set in UPDATE statement using SQL functions, for example, now(), current_timestamp() etc. I mean UPDATE T SET version=now();

After reading a lot of information I think about such solution:

  1. Start SQL transaction with read committed isolation level.
  2. Execute SELECT statement to get rows of table T
  3. Execute SELECT to get curTimeStamp.
  4. Commit SQL transcation.

However, some RDMS, for example H2 and Posgtresql return same value of now() functions inside one transaction. And as I understand then if some update transaction started before my select transaction AND update transaction finished after select transaction then row.version will be < curTimeStamp.

I am not very good in SQL transactions and I have doubts about my solution. Is this solution correct? If not, please, show my mistakes and right direction.

Community
  • 1
  • 1
Pavel_K
  • 10,748
  • 13
  • 73
  • 186
  • In Postgres you can use `clock_timestamp()` instead. That will advance inside a transaction. https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT –  Apr 20 '17 at 17:39
  • @a_horse_with_no_name Thank you for such useful information. But I need a solution for both Postgresql and H2. H2 seems doesn't support such feature http://stackoverflow.com/questions/34478259/current-moment-in-h2-database . So maybe it is better to implement version control on application level instead of database level? Or solution is in general correct? – Pavel_K Apr 20 '17 at 17:48
  • http://stackoverflow.com/questions/34478259/current-moment-in-h2-database – Vao Tsun Apr 20 '17 at 18:32

0 Answers0