XMLPort locks the whole Table

jensmurer

Hello,

we're importing a large file (~4 million lines) via XMLPort into NAV2017.
This should be done multiple times a day but it it seems the XMLport locks the whole table rather than the one line. Because the table is linked into the Item/27-table working with items is more or less impossible while the XMLport is running because every other click results in a "table is locked by another user"-message.
The Transactiontype ist set to default UpdateNoLocks.
Is there a way to change the XMLport-behavior or some kind of programming pattern (temp-tables, etc.) to avoid the locking of the whole table?

jensmurer

I put a COMMIT every other 10,000 lines into the OnAfterInsert-Trigger of the XMLport. That seems to do the trick, even though I doesn't look very clean to me.

Slawek_Guzek

The problem here is not NAV but SQL Server lock escalation mechanism. SQL Server will escalate locking level from record to table lock once you modify or insert 5000 records into it.

This threshold is hardcoded into SQL Server Storage Engine, you cannot do anything about this, the only option is committing imported data at intervals as you did.

jensmurer

I put a COMMIT every other 10,000 lines into the OnAfterInsert-Trigger of the XMLport. That seems to do the trick, even though I doesn't look very clean to me.

Slawek_Guzek

The problem here is not NAV but SQL Server lock escalation mechanism. SQL Server will escalate locking level from record to table lock once you modify or insert 5000 records into it.

This threshold is hardcoded into SQL Server Storage Engine, you cannot do anything about this, the only option is committing imported data at intervals as you did.