title: Can SQL Server 2016 Rearrange Order of Multiple Queries in One Transaction?
body: <p>I experienced a very strange occurrence relating to a multi-query transaction. After SQL Server was updated from 2008 to 2016 (with no warning from our host), we started dropping data after it was posted to the API. The weird thing is, some of the data arrived, and some didn’t.</p>
  <p>In order to protect integrity, the queries are all joined in one transaction. The records can be created and then updated at a later time. They are formatted similar to this:</p>
  <pre class="lang-sql prettyprint-override"><code>DELETE FROM table_1 WHERE parentID = 123 AND col2 = 321;
  DELETE FROM table_2 WHERE parentID = 123 AND col2 = 321;
  -- etc
  INSERT INTO table_1 (parentID, col2, etc) VALUES (123, 321, 123456);
  INSERT INTO table_2 (parentID, col2, etc) VALUES (123, 321, 654321);
  -- etc
  </code></pre>
  <p>There could be hundreds of lines being executed. Due to design, the records in question do not have unique IDs, so the most performant way to execute the queries was to first delete the matching records, then re-insert them. Looping through the records and checking for existence is the only other option (as far as I know), and that is expensive with that many records.</p>
  <p>Anyway, I was struggling to find a reason for this data loss, which seemed random. I had logs of the sql queries, so I know they were being formatted correctly and they had all the data intact. Finally, the only thing left I could think of was to separate the DELETE queries into a separate transaction and execute first*. That seems to have fixed the problem.</p>
  <p>Q. Does anyone know if these queries could be executed out of order in which they were presented? Do you see a better way I could be writing these transactions?</p>
  <p>* I don't necessarily like this solution, because the delete queries were the main reason I wanted a transaction in the first place. If an error occurs during the second transaction, then all the older matching records have been deleted, but the newer versions are never saved. Living on the edge...</p>
  <p>P.S. One other problem I had, and this is probably due to my ignorance of the platform, when I tried to bracket these queries with <code>BEGIN TRAN;</code> and <code>COMMIT TRAN;</code>, immediately after this script finished, any following queries in the same thread got hung up for about 20-30 seconds or so. What am I doing wrong? Do I actually need these verbs if all the queries are being executed at once?</p>