KNOWLEDGE BASE

Preventing Lock With Microsoft SQL Server Connections


Published: 17 Feb 2017
Last Modified Date: 28 May 2020

Question

How to prevent Tableau Desktop from MS SQL Server database tables locking.

Environment

  • Tableau Desktop
  • MS SQL Server

Answer

Option 1:

Use the Read uncommitted data option when creating the connection. With this option, Tableau is able to run at the read-uncommitted isolation level for reading data.  
Note: The connection will default to the isolation levels set on the database. Your database administrator will still need to manage the isolation levels in order to ensure the desired result.

Option 2:

Create a .tdc file. The following post from the Tableau Community may be helpful when creating this type of .tdc file:  https://community.tableau.com/thread/166245

This solution also requires the database administrator to set the isolation levels in order to ensure the desired result.

Note: Tableau does not test or support TDC files. These files should be used as a tool to explore or occasionally address issues with the data connection. Creating and maintaining TDC files requires careful manual editing, and there is no support for sharing these files.

Additional Information

When reading data, MS SQL Server acquires Sch-S (schema stability) locks during compilation and execution to keep the integrity of the data during long processes. Because of this, the long queries may be blocked when a concurrent transaction of MS SQL Server holds a Sch-M (schema modification) lock on the table. For more information see Behavior when reading data.

Discuss this article... Feedback Forum
Did this article resolve the issue?