The table is locked in the inserts ... the site is unusable (sql server 2008)

Question

I have a background process that is constantly being inserted into the table in real time. It can be thousands of investments per hour (with several updates). The same table is used by the web application to obtain some of the data necessary for display to the user.

Currently, when the background process launches its inserts / updates, the web application seems to be blocked and cannot access the database.

I am wondering what a good solution could be. Should I create another table into which I paste and copy the table into another read-only table? Is there another way?

Any help and suggestions are welcome!

Answer 1

Specifically for SQL Server, you can modify your database to include READ_COMMITTED_SHAPSHOT .

This basically prevents your selected requests from being blocked if there are pending inserts / updates in the data.

Answer 2

Some things to look out for will be:

1) Are your insertions / updates or your favorite requests part of a transaction? If so, make sure you set the most favorable transaction isolation level for your particular situation.

2) ? , . , , .

3) # 2. , ? ? , , . (Query Analyzer ).

4) , , / (.. ) /.

5) , /, ?

6) ? MSDN , . , , , .

Answer 3

Enterprise Edition, . , , "" . - , . : http://technet.microsoft.com/en-us/library/ms191160.aspx

Answer 4

-, READ_UNCOMMITTED . . - , -, , . , (READ COMMITTED?) .

If using READ_UNCOMMITTED is not suitable for you, you can familiarize yourself with row level isolation levels at the version level . For example, snapshot isolation uses an optimistic approach to data modification, which reduces the need for pessimistic row locking.