0

I have a similar question, but it's not clear, so I'm asking myself. Simple. I want to update only when there is a target row, and if it doesn't, it shouldn't be done. (No new rows should be added.)

  1. After Select, check the value and Update
  2. Direct processing via IF EXISTS

In case 2, the official documentation warns of a clear performance issue. Considering the various circumstances, is the 2nd performance issue seriously serious? Or 1 and 2 are similar, but if there is a lot of data, will 2 be at a disadvantage from then on? Wouldn't it be a big problem to use IF EXISTS?

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
바보린
  • 159
  • 1
  • 13

1 Answers1

1

The first option where you select-then-update is invalid because there is no guarantee that the data wouldn't change between the time that you've read it until the time that you update it -- the data isn't locked.

Lightweight transactions (LWTs), also known as compare-and-set (CAS) statements, are expensive because they require four round trips to work. The main difference between LWTs and your select-then-update method is that the row is locked for the duration of the "transaction" so no other threads can update its value.

Since you only want to perform an update if the row exists, then you must specify the IF EXISTS condition. Yes, there is a performance penalty in using lightweight transactions but it is unavoidable in your case where you have a conditional update. It isn't negotiable in your scenario. Cheers!

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23