title: EXEC statement and Transaction Scope
body: <p>This seemed so easy at first but everything I know seems to be wrong again.</p>
  
  <p>Looking at PAQ's the consensus seems to be that EXEC does not start an implicit transaction, you can test this doing :</p>
  
  <pre><code>create procedure usp_foo
  as
  begin
    select @@trancount;
  end
  go
  
  exec usp_foo;
  </code></pre>
  
  <p>which returns 0.</p>
  
  <p>If you however step through this with the T-SQL debugger @@Transaction is in fact 1 inside the procedure according to the watch, although it does return 0 ... </p>
  
  <p>So I think this is a side-effect of the debugger, but then I write some code to test it, do an update in a table and then select max (id) out the, the classic :</p>
  
  <pre><code>create table nextid
  (
    id int
  )
  
  insert into nextid values (0)
  
  create procedure nextid
  as
  BEGIN
    UPDATE nextid set id = id + 1
    select max(id) from nextid
  END
  </code></pre>
  
  <p>So I am expecting this to give out duplicate id's as executed in parallel 2 updates can complete before the 2 selects fetching the last id and returning the same value, but try as I might hitting it from multiple machines I cannot make it break. When monitoring the locks and transactions on the machine it reports that the exec is happening in a transaction and importantly, all the statements inside the exec are treated as one unit of work/one transaction.</p>
  
  <p>I would understand if the update was in a transaction and this was the cause of the lock, but the lock seems to remain until after the select.</p>
  
  <p>If I trace with profiler I can see that a transaction ID is provided for the entire execution of the EXEC statement, and transaction ID is not 0 as I would expect while executing ...</p>
  
  <p>Can someone please explain to me where I am missing the plot or am I wrong and it is in fact safe to generate ID's like that?</p>