CODEWITHSUNDEEP
sqlsql-serverperformanceisolation-level
Luca Petrini
Luca Petrini

Reputation: 1745

SQL Server select query lock insert. Why?

I have two SQL select queries executed on SQL Server 2008 R2 without explicit transactions

Query 1:

SELECT * 
FROM MyTable 
WHERE Field1 = 'XXX' AND Field2 = 'YYY'

Query 2:

SELECT * 
FROM MyTable 
WHERE Field1 = N'XXX' AND Field2 = N'YYY'

"Query 2" is faster than "Query 1", but while "Query 2" is running I can't write (INSERT) new records on "MyTable"..."MyTable" seems locked. Instead, while "Query 1" is running I can add new records quietly.

Note: "MyTable" has a clustered composite key (two columns) and other INDEXES.

Can you explain why this behavior?

Thanks.

Upvotes: 3

Views: 133

Answers (1)

Pouria Sharif
Pouria Sharif

Reputation: 166

Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:

ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);

Upvotes: 1

Related Questions