Choose from a table that is constantly inserted into

Question

How am I going to grab data from a table that will CONSTANTLY be inserted into (and should be) without causing a lock, so the inserts will continue to be ignored.

I looked around and found the option with the nolock option, but, if I understand correctly, this does not stop the creation of the lock, but bypasses the current locks and captures everything?

Thanks.

EDIT: This table will never be UPDATED, only INSERTS AND SELECTION

Answer 1

As long as you don't mind getting dirty reads from your table, this should not be a problem for you. Make sure that the translation isolation level is set correctly and that your call code (if applicable) does not use implicit transactions, and you should be fine.

Microsoft transaction isolation documentation: http://msdn.microsoft.com/en-us/library/ms173763.aspx

NOLOCK is a common and, in my opinion, abused option when working in such situations. While this can help you overcome problems in highly competitive situations, it can also make tracking errors difficult. Although this is a bit of an ongoing argument, check out http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx for the idea of ​​some risks through such hints.

Answer 2

You can use the NOLOCK hint when choosing from a table. There are some side effects like this (you can mostly get a dirty read.)

NOLOCK does not cause row locks in the query to which you added it, and does not affect locks issued by other running queries. NOLOCK releases the Sch-S lock, locking the circuit stability, which will not cause you a problem.

Answer 3

I believe that you misunderstood. select ... with (nolock) will not receive any locks. That is, it will not block other entries.

It seems like the downside is that it will include uncommitted reads, so the result may not hold write transactions.

Answer 4

You can use NOLOCK, but I would recommend it only in cases where you know that "dirty data" is permissible (for example, the syslog database in which you know the data will never be modified or deleted after its installation). The best way to do this is to SELECT from data that is NOT blocked; can you identify strings that your insert is not affecting? For example, if your data is inserted with the default CreateDate column equal to GETDATE (), make sure your queries retrieve data from BEFORE this point.

Of course, it all depends on how much data is written, and regardless of whether the insert operator creates string or block pages or tables ...

Answer 5

One option not discussed here is to use replication. If you copy this table and run your queries in the replicated database, you will not block inserts / updates. (In your case, I would use transactional replication - https://msdn.microsoft.com/en-us/library/ms151176.aspx ).