I fullly understand why can not set "auto commit off" in sqlserver

This is xxxxx

 

   Because  MES guy mistaken ,  the data was wrong and made system error then.   After that I plan to set "auto

commit off"  in sqlserver as default.  However, I totally understood why can not set "auto commit off" in sqlserver after testing.

   The most important reason is "allow snapshot isolation".  Our default options is "OFF".  That options means "UNDO".

  If the option is "ON", then  we can do uncheck  IMPLICIT_TRANSACTIONS  to force MES guy to use commit or rollback on

each DML statement.

  Unfortunately, default option is "False", which means  if we want to uncheck  IMPLICIT_TRANSACTIONS  and use SQL as

oracle way. It definately make table lock.

   MES guy usually  with (nolock) at select SQL statement, That is duty read , It can read uncommitted data, which lowest isolaton

level.  You may say, well let do as read commit and auto commit OFF.

   But  under allow snapshot isolation =OFF,  that  makes table lock and select  options also be hold by uncommit DML.

I can safety  use  auto commit OFF by allow snapshot isolation =ON.  But all instance will be slow down because of it.

   Total in all,  we will use  safety procedure to execute DML.

 

Hi..xxxxx

As you mention “undo”, that is not “allow snapshot isolation” but “read committed snapshot”

So in my opinion, we must not turn  “allow snapshot isolation” ON.

Supposing we do that, we would suffer from very slow transaction,

Because “allow snapshot isolation” is mixing two method, UNDO and with(nolock), so it make transaction cost more expensive.