0

I've been asked for a school exercise to write the following query:

SELECT ACCOUNT_ID, AVG(AMOUNT)
FROM ACCOUNTS A
INNER JOIN TRANSACTIONS T ON A.ACCOUNT_ID = T.ACCOUNT_ID
GROUP BY ACCOUNT_ID

The focus of the exercise is to perform the whole statement without including new transactions registered while it is in progress. My first thought was to use the highest isolation level Serializable or set the transaction Readonly. But I'm wondering however if it's really necessary.

Can be a single statement like this considered atomic? If it's the case, does it mean that the engine works on the datas as they are when the query starts?

If it's not the case, what is the correct way to do that?

John Conde
  • 217,595
  • 99
  • 455
  • 496
Shepard
  • 801
  • 3
  • 9
  • 17
  • 1
    Are you using MySQL or SQL Server? They are not the same thing. – John Conde Sep 25 '14 at 20:37
  • SQL Server, how does it change on MySQL? – Shepard Sep 25 '14 at 20:39
  • 1
    google `SQL Server Transaction Isolation Level` and you will be able to chose the best one once you have read and understood it. Your teacher doesnt want you to fix this query for him/her, they want you to learn and understand about all the transaction Isolation Levels :) – M.Ali Sep 25 '14 at 21:24
  • I've already understood that the right isolation level here is Serializable, because it prevents new rows to be added both on Accounts and Transactions. But my question goes beyond that, it's about atomicity of that query. – Shepard Sep 26 '14 at 11:08

0 Answers0