As a best practice, a database system may lock tables in the data source when Tableau issues queries to create or refresh extracts. Table locks help prevent any updates or changes to the data source from happening while the extract queries are running. This ensures that extracts are complete and produce expected results. Depending on the size of the data source and the frequency of updates, some extract operations can take more time, however. Because longer extract operations can make data source tables unavailable for longer periods of time, table locks may interfere or delay other processes that rely on the same tables.
In cases like this, you may be able to configure the degree of locking, also known as the isolation level. By configuring the isolation level you can control the degree of locking of the data source tables during the extract process. The isolation levels are listed below in order of strictness, from the lowest level of locking to the highest level of locking:
- Read Uncommitted
- Read Committed
- Repeatable Reads
- Serializable
Lower isolation levels offer improved concurrency, but limited use of locks can sometimes result in an inconsistent representation of the data. Higher isolation levels guarantee a consistent representation of the data, but use more locks and therefore limit the concurrency you can have among other processes that rely on the same data source tables. For more general information about isolation levels, see the Isolation Levels in the Database Engine article on the Microsoft TechNet site.
Data source connections and isolation levels
The isolation level you can set depends on the data source. You can set the isolation level for the following data source connections:
- IBM DB2: You can specify any of the above four isolation levels through a .tdc file via SQL.
- Microsoft SQL Server: For a SQL Server connection, you have two ways to set the isolation level. You can change the isolation level from the database default (Serializable) to Read Uncommitted during the initial connection to SQL Server. Alternatively, you can specify any of the above four isolation levels through a .tdc file via SQL.
- MySQL: You can specify any of the above four isolations levels through a .tdc file via the ODBC API or SQL.
- Oracle: You are only allowed to set one of two isolation levels for an Oracle connection: Read Committed and Serializable. You can specify these isolation levels through a .tdc file via the ODBC API or SQL.
- Generic ODBC: By default, Tableau sets the isolation level for generic ODBC connections to Serializable via the ODBC API. Although you can set any of the above four isolation levels using a .tdc file, to override the default setting of Serializable, you must also add CAP_ISOLATION_LEVEL_SERIALIZABLE with value=’no’. Furthermore, if you want to set the isolation levels via SQL instead of the ODBC API, you must add CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API with value=’no’ to the .tdc file.
Isolation levels can be set by customizing the data source connection. To customize a data source connection, you must create a Tableau datasource customization (.tdc) file. This .tdc file must contain one of two mechanisms to specify whether you want to set the isolation level via SQL (CAP_SET_ISOLATION_LEVEL_VIA_SQL) or the ODBC API (CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API), and one of the following capabilities:
- CAP_ISOLATION_LEVEL_SERIALIZABLE
- CAP_ISOLATION_LEVEL_REPEATABLE_READS
- CAP_ISOLATION_LEVEL_READ_COMMITTED
- CAP_ISOLATION_LEVEL_READ_UNCOMMITTED
Important: Tableau does not test or support .tdc files. These files should be used as a tool to explore or occasionally address issues with your data connection. Creating and maintaining .tdc files require careful manual editing and there is no support for sharing these files.
A .tdc file can only apply to a single data source and defines the capabilities for a particular data source, which Tableau identifies by the vendor and driver name of the data source provider. For example, a .tdc file for a MySQL data source connection like the following:
This .tdc file must be saved in the “datasources” folder. For example:
- For Tableau Desktop: My Tableau Repository\Datasources
- For Tableau Server: C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources