1

I am trying to update a row and get its contents at the same time. I am using the following query:

UPDATE users SET number=number+1 OUTPUT DELETED.* WHERE ID=?

I could do this in 2 queries, but I want to keep things atomic to avoid a race condition.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Gapjustin
  • 11
  • 1
  • is there a problem with this query, then? P.S. If you used a transaction round 2 queries it would effectively be atomic. – ADyson Jun 07 '17 at 10:26
  • @ADyson this query doesn't work with mySQL as it doesn't support it, I'll look into transactions thank you! – Gapjustin Jun 07 '17 at 10:36
  • Just quickly going over it I have a quick question on it, if I start a transaction and before its closed another query is inputted and then the transaction is rollbacked, wouldn't that cause the inputted query to also be rolled back? – Gapjustin Jun 07 '17 at 10:38
  • no because the transaction only rolls back what's done within it. That's the entire point. If the 2nd query was done outside a transaction, or within the context of a different transaction (even if it's same piece of code being used, the context is the context in which it's _executed_, so two different executions of the same code are different transaction contexts) then a rollback of the 1st transaction would not affect it. – ADyson Jun 07 '17 at 10:42
  • AFAICT, MySQL 8.0 doesn't support `OUTPUT DELETED.*` as part of its syntax for an [UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html) statement. Which DBMS does support the notation? – Jonathan Leffler Jun 07 '17 at 14:30
  • Ended up just adding a boolean collumn to see if it's being used. Thanks for the suggestions though! – Gapjustin Jun 08 '17 at 19:42

0 Answers0