CODEWITHSUNDEEP
c#asp.netsql-server
Raymond Morphy
Raymond Morphy

Reputation: 2526

Which transaction isolation Level should I use to prevent inserting data while selecting data?

I have three tables , table A and TableB and Table C.

I want to read data from table A and join it with table B and insert the result in Table C. I don't want any other transactions can insert any records in table A while I'm joining TablA and Table B.

Which isolation level should I use? Is using read committed isolation level right or not?

Upvotes: 4

Views: 2058

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294277

You are asking the wrong question.

You shouldn't block inserts, why would you want to do that, specially in a concurrent environment like you describe? You'll only see blockage and deadlocks.

Instead you should ask How can I ensure that the join between A and B is consistent ? Meaning that the join will not see any record inserted during the join, without blocking said inserts. And the answer is use SNAPSHOT ISOLATION.

With SNAPSHOT ISOLATION each time you run the join you will see only rows that were already committed when the join started. Rows that were inserted (in A or B) after your join started are not visible. So the join is always consistent. Important thing is that you do not block inserts, and you won't deadlock either. Sounds too good to be true? Of course there is no free lunch, snapshot isolation has a cost, see Row Versioning Resource Usage.

This is a good read on the topic: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide.

Upvotes: 3

Ajay2707
Ajay2707

Reputation: 5798

Hi I will also go with Serializable. 

Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
Read committed (Database Engine default level)
Repeatable read
Serializable (the highest level, where transactions are completely isolated from one another)*

Refer link which clearly show which will be used :-

http://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx

Upvotes: 0

usr
usr

Reputation: 171178

Instead of blocking inserts, consider using snapshot isolation for reads. That way you get a point-in-time fully consistent and stable snapshot to read from. Concurrent DML does not disturb your reads.

If you need to block inserts, SERIALIZABLE is the minimum required level. You might well suffer from blocking and deadlocking. Therefore, I recommend snapshot isolation if you can at all use it.

Upvotes: 0

Sunil
Sunil

Reputation: 150

No, go for the Serializable option which is the best in your present scenario as they are use to prevent the user from adding new records being added to the table.

Upvotes: 1

3dd
3dd

Reputation: 2530

You should use Serializable from the MSDN

"A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete."

See http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx for details

Upvotes: 0

Related Questions