title: Why the following t-sql transaction does not work as expected?
body: <p>To test how a transaction work, I wrote the following t-sql. As the first attribute is the primary key, none of the insertions should be committed.
  But, the first insertion is committed? Why?</p>
  
  <pre><code>begin transaction
     insert into instructor
     values ('99999', 'Yellow', 'Biology', 700000)
  
     insert into instructor
     values ('99999', 'Blue', 'Statistics', 85000)
  commit;
  
  select * from instructor where ID = '99999'
  
  --delete from instructor where ID = '99999'
  </code></pre>
  
  <blockquote>
    <p>(1 row affected) Msg 2627, Level 14, State 1, Line 100 Violation of
    PRIMARY KEY constraint 'PK__instruct__3214EC278C8DA99F'. Cannot insert
    duplicate key in object 'dbo.instructor'. The duplicate key value is
    (99999). The statement has been terminated.</p>
  </blockquote>
  
  <p>Completion time: 2019-11-06T14:02:27.3436411+02:00</p>