0

When profiling our Microsoft Dynamics NAV application with SQL Server Profiler, I get INSERTs with the REPEATABLEREAD hint.

What effect does the REPEATABLEREAD hint has on an INSERT statement?

In SQL Server 2012 - How does "Repeatable Read" isolation level work? the REPEATABLEREAD hint is described, but this seems to apply to SELECTs only.

Extra info:

  • Not all INSERTs have the REPEATABLEREAD hint, so maybe Microsoft Dynamics NAV application issues the hint under specific circumstances.
  • The INSERTs are simple INSERT statement (no SELECT INTO or JOINs). The execution plan also shows a simple INSERT
Improve this question
5
  • 1
    Does this answer your question? SQL Server 2012 - How does "Repeatable Read" isolation level work? or perhaps Does REPEATABLE_READ isolation prevent inserts for non-ranged queries? Commented Aug 31, 2020 at 14:50
  • Hi underscore_d, thanks for your info. The answer states 'No. The REPEATABLE_READ isolation level only guarantees that you will see the same data if you repeat the SELECT' So the hint is used for SELECT, not for INSERT Commented Aug 31, 2020 at 14:57
  • Do the insert statements have sub selects/subqueries? Commented Aug 31, 2020 at 15:14
  • Hi D Kramer, no its just a plane INSERT statement. Btw: I have updated my question based on your remark, thanks! Commented Aug 31, 2020 at 15:35
  • The documentation states that the REPEATABLEREAD hint "Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL)." So, if the INSERT must scan indexes to check for dupes etc. - maybe that's what it means Commented Aug 31, 2020 at 16:42
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.