12 Replies Latest reply: Sep 1, 2014 10:37 AM by joão lopes RSS

    Does Qlikview lock tables during load??

      I have an SQL SELECT (on MySQL via ODBC) that takes a long time while loading. The strange behaviour is that all the MySQL tables used for the load are locked, so that other user can't access this tables. So my question: Does Qlikview lock tables during an sql load? And if yes, where can I found the settings to turn of locking tables.

      Any information is strongly appreciated.

      Thomas

       

        • Does Qlikview lock tables during load??
          carlosmartinez1

          Dear tbarnow:

          Yes, if the tables are lock once the Qlikview Reload finish it is possible when Qlikview detect a circular reference, this happen when you have a bad structure of your model and you have a lot of synthetic key. You can found more info in some post here in the forum.

           

          Regards

          Orlando

            • Does Qlikview lock tables during load??

              Orlando, thanks for your answer, but I guess they didn't get the question. It's not a question what qv do AFTER an SQL LOAD, it's a question on HOW DOES qv operates an SQL LOAD. Or to make my question clearer: Does qv send a LOCK TABLE command to the database when it's starting to retrieve the data.

                • Does Qlikview lock tables during load??
                  Rob Wunderlich

                   


                  tbarnow wrote: Does qv send a LOCK TABLE command to the database when it's starting to retrieve the data.


                  As John clarified, QV only passes through the SQL SELECT to the ODBC driver. The ODBC driver and the target DBMS control locking. Default locking in MySQL depends on which table engine you are using. MyIsam uses table level locking, InnoDb uses row level locking. Are you seeing a table level lock? If MyIsam, that's what you're going to get. If InnoDb, it's possible that the ODBC driver issues a LOCK TABLE, I don't know.

                  The lock will be held until all the rows are returned to QV. You might try using the MySql hint SQL_BUFFER_RESULT to write the results to a temp table before returning data to the application (QV). I think the syntax is

                  SELECT SQL_BUFFER_RESULT * FROM mytable;

                  The lock will be released when the temp table is filled. This may make the overall query run slower, but will reduce the lock duration. That would be especially significant if you have a slow network connection between QV and the MySql server.

                  -Rob

                    • Does Qlikview lock tables during load??

                      Hi Rob, thanks a lot for your answer. I figured out that I probably do not have a problem of locking tables, but a problem on how MySQL handles internally single threads. So it can happen that a long time runnig sql statement blocks all threads which comming after the thread of the long time running sql (which could be of course of a locking the table touched by the sql statement).

                      But it's very interessting what you wrote about the used table engine. So I didn't know that the MyISAM engine provokes a table level locking.

                      I will investigate more deeper the behaviour, but for me it looks like I have to use a second server where there is no daily work done with.

                      Thanks a lot for your input, it will help me to figure out much better.

                      -Thomas

                • Does Qlikview lock tables during load??
                  Peter Rieper

                  Hi,

                  you may use also the NOLOCK-option in your SQL-command.

                  HTH
                  Peter

                    • Does Qlikview lock tables during load??
                      John Witherspoon

                      It's probably going to depend on your DBMS and perhaps your ODBC driver (or whatever driver you're using). In DB2, for instance, you must specify a "FOR FETCH ONLY" clause to indicate that you don't want to lock the rows.

                        • Does Qlikview lock tables during load??

                           


                          John Witherspoon wrote:
                          It's probably going to depend on your DBMS and perhaps your ODBC driver (or whatever driver you're using). In DB2, for instance, you must specify a "FOR FETCH ONLY" clause to indicate that you don't want to lock the rows. <div></div>


                          Hi John, I'm using the original MySQL ODBC driver, and unfortunatly there is no option to change.

                          But the question is: Does qlikview send an LOCK TABLES command for the specified tables of the SELECT command, or not.

                           

                            • Does Qlikview lock tables during load??
                              John Witherspoon

                               


                              tbarnow wrote:Does qlikview send an LOCK TABLES command for the specified tables of the SELECT command, or not.


                              While I can't answer with 100% certainty, QlikView very likely sends NOTHING to your ODBC driver (and through it to your DBMS) that you don't explicitly specify between "SQL" and the next semicolon. There would be no reason to, and attempting to do so is just begging for failure. For locks specifically, there is strong reason NOT to do so. It would be insane to put in the programming effort in order to do the exact opposite of what you need.

                              As far as the "for fetch only" clause I mentioned for DB2 (which likely isn't the same for MySQL), you write it explicitly in the SQL. It's not some ODBC driver configuration.

                              MyTable:
                              LOAD *;
                              SQL SELECT A, B, C
                              FROM TableX
                              WHERE D = 'Something'
                              FOR FETCH ONLY
                              ;

                              I'm not sure if this applies to MySQL, but for DB2, you can specify an "isolation level". One is "repeatable read", and the other is "cursor stability". Repeatable read will lock your data. Basically, one is a dirty read, and the other is a clean read. One just takes what it finds at the time it runs, while the other waits for all other locks to be released, locks the rows itself, and then returns the data to you. That's my understanding, anyway. Not sure if there is something similar in MySQL, and if it is configurable if so, either in the DBMS or in the ODBC driver. Just something else to possibly look for.

                                • Does Qlikview lock tables during load??

                                  Hi John,

                                   


                                  John Witherspoon wrote:
                                  ...QlikView very likely sends NOTHING to your ODBC driver ...


                                  Thats what I'm assume, but I have this to ashure against my senior (you know probably how things run in an development divison Wink ).

                                   


                                  John Witherspoon wrote:
                                  As far as the "for fetch only" clause I mentioned for DB2 (which likely isn't the same for MySQL), you write it explicitly in the SQL. It's not some ODBC driver configuration.
                                  MyTable:
                                  LOAD *;
                                  SQL SELECT A, B, C
                                  FROM TableX
                                  WHERE D = 'Something'
                                  FOR FETCH ONLY
                                  ;


                                  Unfortunatly there's no analog statement in MySQL. Normally it's the other way around: you have to specify when you want to lock tables just with statements like FOR UPDATE or LOCK IN SHARE MODE.

                                  But it depends, like Bob wrote, also on the way how tables are implemented (MyISAM, InnoDB) in MySQL, which I didn't know before. So I guess, thats where my problem is situated.

                                  Thanks a lot for your input, I guess it will help more then me alone!

                                  Thomas

                            • Does Qlikview lock tables during load??

                               


                              Peter Rieper wrote:
                              Hi,
                              you may use also the NOLOCK-option in your SQL-command.
                              HTH
                              Peter<div></div>


                              Unfortunatly there is no UNLOCK option in MySQL SELECT syntax.

                              Thomas

                               

                            • Re: Does Qlikview lock tables during load??
                              John Tackman

                              You can select without locking in MySQL by calling the select like this:

                               

                              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
                              SELECT * FROM TABLE_NAME ;
                              COMMIT ;

                               

                              You can find more extensive documentation here:

                               

                              http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html