Optimized Locking feature vs Dynamics 365 Business Central
Optimized Locking in SQL Server 2025 requires ReadCommitted transaction isolation, but Dynamics 365 Business Central uses RepeatableRead by default, preventing the feature from working effectively. The post examines tri-state locking, RCSI, and transaction isolation levels in both online and on-premises deployments.
post Dynamics 365 Business Central tales, myths, legends. And something serious.
"Optimized Locking feature needs ReadCommitted (4096) transaction isolation - or any row versioning-based isolation levels - to effectively work at its best"
Key points
- Tri-state locking and ReadIsolation in BC apply only to in-transaction single reads, while the default isolation level remains RepeatableRead
- Optimized Locking feature needs ReadCommitted isolation to function properly, but BC transactions typically use RepeatableRead (65536)
- BC 2024 Wave 2 made tri-state locking with RCSI the default for all deployments
- Web service and job queue sessions show different isolation levels like ReadUncommitted (256)
- The product group should consider changing BC’s default isolation level from RepeatableRead to ReadCommitted for better optimization
Quotes
- “Optimized Locking feature needs ReadCommitted (4096) transaction isolation - or any row versioning-based isolation levels - to effectively work at its best” (This explains the core incompatibility preventing BC from leveraging SQL Server 2025’s new Optimized Locking capabilities)
- “Hence, sorry but Optimized Locking feature won’t kick in its full potential with almost all the typical Dynamics 365 Business Central transactions.” (This is the main conclusion showing that BC cannot fully benefit from the new feature without architectural changes)
Context
- Features: Optimized Locking, Tri-state locking, Read Committed Snapshot Isolation (RCSI), AL ReadIsolation statement, Transaction isolation levels
- Versions: 2024 Wave 2, v25
Source: Duilio Tacconi, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.