snapshot isolation level usage

• rajemessage 14195

  Hall of Fame

  Points: 3299

  More actions

  February 5, 2014 at 10:24 am

  #285154

  Dear All,

  There are some transaction tables in which more than one user add and update records (only).

  what ever they add and update in transaction tables, based on that entry they add a record in Table A1

  , Table A1 has two cols one keeps the table name of transaction table and other col keeps the pk(primarykey) of transaction tables.

  So Table A1 always gets only inserts,

  Table A1 gets entry only for transaction tables , and only when transaction table gets entry .

  At the same time there is a process (ts) which reads Table A1 on time basis, picks up all records

  form Table A1 and reads data from transaction tables on the basis of PK stored in it . there it after inserts all the read records into a new temp table.

  and at the end of transaction it deletes records from Table A1.

  after some time it again picks up new records from Table A1 and repeats the process.

  For process (ts) . i want to use ALLOW_SNAPSHOT_ISOLATION so that user can keep on entering records.

  Q1) The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before one can start a transaction that uses the SNAPSHOT isolation level. I wanted to know should i set the option to OFF after the process(ts) is complete, and switch it on again on the database when process(ts) starts again.

  that is, keeping it on all the time will affect the database in any case?

  Q2) ALLOW_SNAPSHOT_ISOLATION ON , will affect other isolation level's transactions or only to snapshot isolation levels transactions. that is, i have old stored proc and front end applications like web or window on .net which are using default isolation levels.

  Q3) is my choice of isolation level for process(ts) is correct or there can be any other solution.

  Note: "the information is quite less but i wont be able to give full information."

  yours sincerely