SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

snapshot isolation level usage

snapshot isolation level usage

rajemessage 14195
rajemessage 14195
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 348
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


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum