insert data to table and select data from view at the same time

Question

Post reply

insert data to table and select data from view at the same time

dlam 18073

SSC-Addicted

Points: 438
More actions
November 15, 2010 at 11:35 pm

#231699

Hi everyone,

i got a application that connects to sql server

user can use the application to insert new records to tblA

(insert 500 more records everytime)

other user can select data from the view of tblA

my question:

if this two processes happen at the same, is it going to cause concurrency problem?

as I know, two processes get(read and modify) the same record(s) at the same time in a table will cause concurrency problem.

but what about table and view ?

if it will cause concurrency problem, what kind of lock I can use?

thx anyone can give an idea?!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

EnjoySQL SSC Veteran Points: 267 More actions · Answer 1

There are many ways to handle the concurrancy issues, following are few tips to start with :

1. If you are concerned about dirty reads, you could use snapshot isolation to resolve this. Please read the microsoft kb on isolation level in sql server.

2. If you are not concerned about dirty reads you could use NOLOCK hint in your select to avoid get into the lock.

Paul White SSC Guru Points: 150457 More actions · Answer 2

dlam 18073 (11/15/2010)

but what about table and view?

Using an (non-materialized) view makes no difference. Such a view is just a description of a query - no data is stored in the view itself, so there's nothing to place a lock on. Data locks are taken on the underlying table.

if it will cause concurrency problem, what kind of lock I can use?

The right answer for your system depends on your exact situation.

As the previous poster mentioned, you could consider enabling one of the row-versioning isolation levels. This allows readers to read a consistent view of the data without blocking concurrent writers, and vice-versa. For more information, see this link:

http://technet.microsoft.com/en-us/library/ms177404(v=SQL.100).aspx

I would generally advise against using NOLOCK hints. If you can tolerate reading data that has not committed (and might still be rolled back) set the isolation level to read uncommitted instead.

A third option is to use the READPAST table hint. See http://msdn.microsoft.com/en-us/library/ms187373(v=SQL.100).aspx for more information on that.

Your final option is to do nothing. Keep the inserting transactions short and efficient, and you may never notice a blocking problem. As I say, the right choice for you depends on lots of other considerations that we cannot know about.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dlam 18073 SSC-Addicted Points: 438 More actions · Answer 3

Paul White NZ (11/16/2010)

dlam 18073 (11/15/2010)

but what about table and view?

Using an (non-materialized) view makes no difference. Such a view is just a description of a query - no data is stored in the view itself, so there's nothing to place a lock on. Data locks are taken on the underlying table.

Yes, a view doent store any data, a view get data from table. So if I need to place a lock on the reader side (a view in this case), how can I do that?

if it will cause concurrency problem, what kind of lock I can use?

The right answer for your system depends on your exact situation.

As the previous poster mentioned, you could consider enabling one of the row-versioning isolation levels. This allows readers to read a consistent view of the data without blocking concurrent writers, and vice-versa. For more information, see this link:

http://technet.microsoft.com/en-us/library/ms177404(v=SQL.100).aspx

It sounds a good idea. In this application, only one user can insert data, all other users select data from a view.

Question: when everytime a records insert, does it going to overwite the old version, or it will create a new version everytime?

Your final option is to do nothing. Keep the inserting transactions short and efficient, and you may never notice a blocking problem. As I say, the right choice for you depends on lots of other considerations that we cannot know about.

I really consider this before. But something I am not sure, if concurrency rises, what is going to happen? how I am going to to know? does the transaction stop or a error message popup?

one other thing, I really need information(in detail) about locking, concurrency and coding(like how to set lock, isolation levels), does any website, book or articles can give me help?

thx ppls reply my post and give me ideas!