Does Qlikview lock tables during load??

[#1]

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

[#2]

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

[prieper]

Hi,

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

HTH
Peter

[johnw]

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.

[#5]

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.

[#6]

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

[#7]

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.

[johnw]

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.

[rwunderlich]

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

[#10]

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 ).

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