6 Replies Latest reply on Aug 14, 2018 7:25 AM by leighton.du

    Database locking

    leighton.du

      Hi All,

       

      I'm trying to understand Tableau and have a quick question about SQL database locking. I understand there is a write-lock on the database while Tableau is making calculations or creating an extract (right?) during a live connection, is the write-lock only on the table being used (at that exact moment) or on the entire SQL database it's connected to? Moreover, there is a delay in server response time when I execute SQL scripts (just some select statements) compared to usual, is it expected that read-time is also delayed?

       

      Thank you.

        • 1. Re: Database locking
          Alex Braun

          The lock is just to the table(s) you are connecting to. When connecting to your data source, there is a checkbox called Read Uncommitted Data and that will run with query without locking the table.  The only downside is that information can change while you are in process of reading it.

          1 of 1 people found this helpful
          • 2. Re: Database locking
            Ken Flerlage

            The locking mechanisms will often depend on the database platform and how it's configured. For example, I use a lot of SQL Server databases and generally set their default to READ COMMITTED SNAPSHOT which will read a sort of in-memory snapshot of the data, so that it does not have to lock the table, but will still not get "dirty reads" (i.e. changing data).

             

            But what is the real problem here? Are you having performance issues with the extract? If it's impacting other processes on the database then that's likely the case. I'd suggest taking a look at your query plans to see where you can make improvements. You might be able to add a few indexes that help a lot.

            1 of 1 people found this helpful
            • 3. Re: Database locking
              leighton.du

              Thanks for the info. I'm just trying to understand the program a bit more and don't have a problem with the tables being locked. Would you happen to know if reading the table is slower while Tableau is calculating during a live connection?

              • 4. Re: Database locking
                leighton.du

                Thanks for the info. I'm just trying to understand the program a bit more and don't have an actual problem. Is the delay (in read time while Tableau is calculating) I see normal/expected though?

                • 5. Re: Database locking
                  Ken Flerlage

                  The query is executing against the database so it will have at least some impact on the database. But the amount of delay depends on the complexity of the query, what indexes are in place, the design of the database tables, and the sizing of the database

                  1 of 1 people found this helpful
                  • 6. Re: Database locking
                    leighton.du

                    Great, that answered all my questions. Thank you!