I have a question about SQL Server and transactions. My application use transactions and I use snapshot isolation level. My problem is that sometimes, for specific SQL statements the execution in different transactions will be blocked. I don't want to commit the transactions because the transaction durations are related to users decision.
In a transactions I insert a record into a table:
INSERT INTO "SEED"."SETS" WITH (ROWLOCK)
("SECT_ID", "SET_ID", "META_SET_ID", "SET_ROW")
VALUES (1838, 89405, 158, 27);
and in another transaction I execute a query like this:
INSERT INTO "SEED"."CELLS" WITH (ROWLOCK) ("SET_ID", "META_CELL_ID")
SELECT "TARGET_SET_ID", "CELLS"."META_CELL_ID"
FROM "SEED"."TT_T_SET_1C8BFA4C_7058"
INNER JOIN "SEED"."CELLS" ON ("CELLS"."SET_ID"="TT_T_SET_1C8BFA4C_7058"."SOURCE_SET_ID")
ORDER BY "TARGET_SET_ID"`
The amount of inserted rows could be thousands.
"CELLS" table is a child of "SETS" table (1 to n relation over "SET_ID" field).
On "SETS" there is an unique non clustered index on "SET_ID" called "SETS_U01".
In "CELLS" there is a clustered index on "SET_ID" called "IX_CELLS_F01".
If i execute the second query the Sql Server engine will be blocked on "SETS_U01" Index Scan even if the transactions was created in snapshot isolation.
If i execute the second query with a batch like this:
DECLARE @rowCount INT = (SELECT COUNT(*) FROM "SEED"."TT_T_SET_1C8BFA4C_7058"
INNER JOIN "SEED"."CELLS" ON ("CELLS"."SET_ID"="TT_T_SET_1C8BFA4C_7058"."SOURCE_SET_ID"))
DECLARE @batchSize INT = 2000
DECLARE @startIndex INT = 0
WHILE @startIndex < @rowCount
BEGIN
INSERT INTO "SEED"."CELLS" WITH (ROWLOCK) ("SET_ID", "META_CELL_ID")
SELECT "TARGET_SET_ID", "META_CELL_ID"
FROM ( SELECT "TARGET_SET_ID", "META_CELL_ID", ROW_NUMBER()
OVER (ORDER BY "TARGET_SET_ID") AS RowNum
FROM "SEED"."TT_T_SET_1C8BFA4C_7058"
INNER JOIN "SEED"."CELLS" "SUB_CELLS" ON ("SUB_CELLS"."SET_ID"="TT_T_SET_1C8BFA4C_7058"."SOURCE_SET_ID") )
AS SubQuery
WHERE SubQuery.RowNum > @startIndex AND SubQuery.RowNum <= (@startIndex + @batchSize)
ORDER BY "TARGET_SET_ID"
SET @startIndex += @batchSize
END
the execution will not be blocked because the execution plan will use a Index Seek on "SETS_U01" every batch size i use even if the batch size is greater than the inserted row amount.
First question: why the index scan is blocked even if the isolation lavel is READ_COMMITTED_SNAPSHOT and the transaction isolation is snapshot?
Second question: why the batch is working? obviously because it use an index seek. Can i use this batch for every INSERT INTO ... SELECT FROM statements?
Table definitions:
CREATE TABLE [SEED].[SETS](
[SECT_ID] [int] NOT NULL,
[SET_ID] [int] NOT NULL,
[SET_ROW] [int] NOT NULL,
[META_SET_ID] [int] NOT NULL,
CONSTRAINT [SETS_PK] PRIMARY KEY CLUSTERED
(
[SECT_ID] ASC,
[SET_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B],
CONSTRAINT [SETS_U01] UNIQUE NONCLUSTERED
(
[SET_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B]
) ON [SEED_M01B]
GO
CREATE TABLE [SEED].[CELLS](
[SET_ID] [int] NOT NULL,
[META_CELL_ID] [int] NOT NULL,
CONSTRAINT [CELLS_PK] PRIMARY KEY CLUSTERED
(
[SET_ID] ASC,
[META_CELL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B]
) ON [SEED_M01B]
GO
ALTER TABLE [SEED].[CELLS] WITH CHECK ADD CONSTRAINT [CELLS_F01] FOREIGN KEY([SET_ID])
REFERENCES [SEED].[SETS] ([SET_ID])
GO
other table definitions:
CREATE TABLE [SEED].[META_CELLS](
[UDA_DOMAIN_ID] [int] NOT NULL,
[META_DOC_ID] [int] NOT NULL,
[META_SECT_ID] [int] NOT NULL,
[META_SET_ID] [int] NOT NULL,
[META_CELL_ID] [int] NOT NULL,
[META_CELL_ROW] [int] NOT NULL,
[META_CELL_COL] [int] NOT NULL,
[META_CELL_DESC] [nvarchar](250) NULL,
[META_CELL_NOTE] [nvarchar](2000) NULL,
[META_CELL_WUOM] [tinyint] NOT NULL,
[NOT_TRIGGERS_AUDIT] [tinyint] NOT NULL,
[UDA_ID] [int] NOT NULL,
[CEM_ID] [int] NOT NULL,
[MCT_ID] [int] NOT NULL,
[PUB_ENABLE_ID] [int] NULL,
[PUB_COLUMN_NAME] [nvarchar](32) NULL,
CONSTRAINT [META_CELLS_PK] PRIMARY KEY CLUSTERED
(
[UDA_DOMAIN_ID] ASC,
[META_DOC_ID] ASC,
[META_SECT_ID] ASC,
[META_SET_ID] ASC,
[META_CELL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B],
CONSTRAINT [META_CELLS_U01] UNIQUE NONCLUSTERED
(
[META_DOC_ID] ASC,
[META_SECT_ID] ASC,
[META_SET_ID] ASC,
[META_CELL_ROW] ASC,
[META_CELL_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B],
CONSTRAINT [META_CELLS_U02] UNIQUE NONCLUSTERED
(
[META_CELL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [SEED_M01B]
) ON [SEED_M01B]
GO
CREATE TABLE [SEED].[TT_T_SET_1C8BFA4C_7058](
[SOURCE_SET_ID] [int] NOT NULL,
[TARGET_SET_ID] [int] NOT NULL
) ON [SEED_U]
GO
The point is not the use of transactions. Our transactions can last 1 second or 1 hour. My application manage documents modified by users. And it works well on other databases. I just want to know how to manage this issue or to know if it is unmanageable. Maybe i have to create indexes in another way or to use table or query hints? Why i can't insert (in some cases) different records on different tables on different transactions in SQL Server using snapshot isolation?
The transaction is being blocked, even under snapshot isolation, because there are foreign keys that need enforcing, and foreign keys are always enforced under SERIALIZABLE isolation.
This issue is then exacerbated by the fact that the estimated number of rows is high enough that the server decided to use a merge join, which means it's scanning the full indexes of child tables. This is not necessarily a bad thing in most cases: it's probably a lot faster than loads of index seeks, but it means it's blocking the whole table.
You might be able to force an nested loops/index seek by bringing down the number of estimated rows in the INSERT. You can do this in a number of ways:
OPTION (LOOP JOIN). This can affect the rest of the plan though.OFFSET FETCH instead of ROW_NUMBER.
DECLARE @batchSize bigint = 2000, @offset bigint, @rc bigint;
WHILE 1=1
BEGIN
INSERT INTO SEED.CELLS WITH (ROWLOCK)
(SET_ID, META_CELL_ID)
SELECT
t.TARGET_SET_ID,
s.META_CELL_ID
FROM SEED.TT_T_SET_1C8BFA4C_7058 t
JOIN SEED.CELLS c ON c.SET_ID = t.SOURCE_SET_ID
ORDER BY t.TARGET_SET_ID
OFFSET @offset ROWS
FETCH NEXT @batchSize ROWS ONLY
OPTION (LOOP JOIN);
SET @rc = @@ROWCOUNT;
SET @offset += @rc;
IF @rc < @batchSize
BREAK;
END;
OPTIMIZE FOR to make the server think it's getting very few rows.
DECLARE @batchSize bigint = 2000, @offset bigint, @rc bigint;
WHILE 1=1
BEGIN
INSERT INTO SEED.CELLS WITH (ROWLOCK)
(SET_ID, META_CELL_ID)
SELECT
t.TARGET_SET_ID,
s.META_CELL_ID
FROM SEED.TT_T_SET_1C8BFA4C_7058 t
JOIN SEED.CELLS c ON c.SET_ID = t.SOURCE_SET_ID
ORDER BY t.TARGET_SET_ID
OFFSET @offset ROWS
FETCH NEXT @batchSize ROWS ONLY
OPTION (OPTIMIZE FOR (@batchSize = 1));
SET @rc = @@ROWCOUNT;
SET @offset += @rc;
IF @rc < @batchSize
BREAK;
END;
You can obviously do the same thing without batching
DECLARE @batchSize bigint = 1000000000;
INSERT INTO SEED.CELLS WITH (ROWLOCK)
(SET_ID, META_CELL_ID)
SELECT TOP (@batchSize)
t.TARGET_SET_ID,
s.META_CELL_ID
FROM SEED.TT_T_SET_1C8BFA4C_7058 t
JOIN SEED.CELLS c ON c.SET_ID = t.SOURCE_SET_ID
OPTION (OPTIMIZE FOR (@batchSize = 1));