Add option for `with (nolock)` on SQL Server Table Queries to prevent deadlocks

Feature Suggestions
1

Some of the tables we use are highly transactional, and it’s necessary to use a with (nolock) when referencing the table. This prevents deadlocks occurring on the table, so is important flag for us to add.

Currently, we get around this by setting out model type as type: 'query' and then setting

  query: @sql
          SELECT * FROM database.schema.table WITH (NOLOCK);;

This is a little cumbersome, so would prefer to be table to use type: 'table' but be able to set a flag to force Holistics to add the WITH (NOLOCK) flag

this might look something like…

Model dbo_table {
  type: 'table'
  label: 'Model Example'
  description: ''
  data_source_name: 'my_sql_server_datasource'
  dimension dim1 {
    ...
  }
  dimension dim2 {
    ...
  }
  owner: 'user_me'
  table_name: '[dbo].[table]'
  nolock: true
}

the executed query will then look like…

SELECT DISTINCT TOP 100
  [dbo_table].[dim1] AS [d1],
  [dbo_table].[dim2] AS [d2]
FROM
  [dbo].[table] [dbo_table] WITH (NO LOCK)

instead of this when using our work-around…

SELECT DISTINCT TOP 100
  [dbo_table].[dim1] AS [d1],
  [dbo_table].[dim2] AS [d2]
FROM
  (
    SELECT * FROM [dbo].[table] WITH (NOLOCK)
  ) [dbo_table]

Of course, users need to understand when and where it’s appropriate to use this flag, as it can have negative impact as well as a positive one.

1 Like