Select and insert at the same moment SQL 2016

Question

I have simple table in my application where is

ID | VALUE | DATE | ITEMID

All the time some items are inserting data threw my webservice to this table. I am inserting only data that is 5 or more minutes old. At the same time when I am using my web application I am selecting some max, min and current values for items. From time to time I got

Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Is there any chance to get data and for that moment when I am selecting, don't care about inserts?

srworksalot · Answer 1 · 2019-04-01T11:19:05.813

2

You can use with(nolock) hint, This will get you uncommitted data.

See the answer here What is "with (nolock)" in SQL Server?

And here http://sqlserverplanet.com/tsql/using-with-nolock

edited Apr 01 '19 at 11:19

answered Apr 01 '19 at 11:12

srworksalot

91
5

if it does i'll appreciate if you'll mark it as the selected answer :) – srworksalot Apr 01 '19 at 11:25
until now client didn't sent any requests that problem is still existsing :) – Chris Apr 03 '19 at 08:45

score 1 · Accepted Answer · edited Mar 16 '22 at 11:12

If using SQL Server 2016:

Problem:

When we write a SELECT statement, shared locks are held on resources - say the TableX from which max, min and current values are being selected. Now for this moment if you INSERT data in these locked tables (TableX), then because INSERT requires an exclusive lock on the resource (TableX), the INSERT statement will wait for the SELECT to finish.

Solution:

As suggested in posts below, we can use WITH (NOLOCK) or WITH (READ UNCOMMITTED) table hints so that SELECT statements don't hold a lock on the underlying resources (TableX). But the problem is, this SELECT might read uncommitted data that was changed during a transaction and then later was rolled back to its initial state (also called reading dirty data).

If you want to make sure that the SELECT only reads committed data and also doesn't block the INSERTs to write to TableX, then at database level SET READ_COMMITTED_SNAPSHOT ON.

score 0 · Answer 3 · edited Mar 16 '22 at 09:22

0

You can change the SELECT query to avoid deadlocks as:

SELECT ITEMID, MAX(VALUE), MIN(VALUE)
FROM table_name WITH (NOLOCK)
GROUP BY ITEMID, DATE

edited Mar 16 '22 at 09:22

CarenRose

1,266
1
12
24

answered Apr 01 '19 at 11:15

sabhari karthik

1,361
6
17

Select and insert at the same moment SQL 2016

3 Answers3