Ever Faced a Table Lock in D365 Finance?

 

Dealing with table locking can be a real productivity blocker! 🛑 If you've run into it, what's your first move?

 

Do you immediately replicate it in your dev environment to isolate the root cause? Do you use SQL scripts to monitor blocking transactions, or maybe hop onto tools to identify exactly where the issue lies?

 

🔧 Here’s a quick idea:

Here’s a one-liner summary for each approach to tackling table locking in D365 Finance:

1. Replicate in Dev: Set up the same conditions in Dev to consistently reproduce the issue.

2. Monitor Blocking Scripts: Run a SQL blocking query to view live blocking sessions.

3. Consider Indexing & Optimization: Adjust indexes or split queries to reduce locking.

4. Analyze Query Execution Plans: Review execution plans to identify locking hotspots.

5. Implement Retry Logic in Code: Use retry logic in code to avoid lock failures.

6. Leverage Batch Processing and Throttling: Process records in batches to reduce lock duration.

7. Set Transaction Isolation Levels: Adjust isolation levels to minimize lock contention.

8. Use SQL Server Monitoring Tools: Use tools like Profiler to identify blocking queries.

9. Optimize Data Access Patterns: Query only necessary data to minimize lock chances.

10. Break Down Complex Transactions: Split transactions into smaller steps to reduce lock duration.

 

👥 Have a different approach? Drop your insights below! Let's share tactics and help each other reduce locking issues for a smoother D365 experience.

Here are some SQL scripts to identify and diagnose locking details.

[Note: Run these only in a Dev environment to prevent any disruption]

1.     1. Identify Active Locks with sys.dm_tran_locks : This query provides an overview of active locks on tables and indexes, helping pinpoint specific locking issues:

 

SELECT     request_session_id AS SessionID,     resource_type AS ResourceType,     resource_database_id AS DatabaseID,     resource_associated_entity_id AS EntityID,     request_mode AS LockMode,     request_status AS LockStatus FROM sys.dm_tran_locks ORDER BY request_session_id;

2.   

2.   2. Detailed Blocking with sys.dm_exec_sessions : This script gives a more comprehensive view of the sessions and users causing blocks, with session details and the current executing SQL

 

SELECT     s.session_id,     s.login_name,     s.status,     r.blocking_session_id,     r.wait_type,     r.wait_time,     r.command,     t.text AS QueryText FROM sys.dm_exec_sessions s JOIN sys.dm_exec_requests r ON s.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.blocking_session_id <> 0 ORDER BY r.wait_time DESC;