How to load from a staging table without introducing a lock?

Harris_Chiu

How to load from a staging table without introducing a lock? We have a staging table, and would like to insert the whole table into the main Fact table without lock. Currently we are just using Insert .. Select statement. From the Manual and in practice, if there is an existing Select (S Lock) on the fact table longer than the timeout (300 seconds by default), the insert would get lock error. We have investigated to use COPY command but it is very restricted to load data from flat files or other database. Is there a way to insert data between tables without introducing/acquiring an I Lock? Thanks

[Deleted User]

Hi Harris, thanks for your question. In answer: All operations that load data into tables, including all INSERT and COPY statements, take an "I" lock. That said, it sounds like what you'd really like to do is to be able to run INSERT and SELECT queries simultaneously. This is quite doable in Vertica. The typical solution is actually to avoid the "S" lock. A stock configuration of Vertica will not take "S" locks by default when executing SELECT statements, unless you or a tool you are using issues statements that change its behavior. Are you possibly running any of your transactions as SERIALIZABLE, rather than the default READ COMMITTED isolation level? The way to achieve what you are trying to do is typically by adjusting the SQL isolation level. See the corresponding documentation pages: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#9334.htm https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#2887.htm Note that you have to COMMIT after changing the isolation level: "S" locks in existing transactions are not released if the transaction's isolation level is changed; they are always held until the end of the transaction. If that doesn't help, could you post more information about the specific lock request that is blocking? You can view lock information via the "LOCKS" system table: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#12262.htm

Harris_Chiu

Hi Adam, Thanks for your reply. As long as the SELECT does not acquire a S lock, it should be good for now. The problem was the ODBC driver ini config has TXReadCommitted flag set to 0 and it's by default. This could be a good change to ODBC driver distributed in Vertica package. Thanks Harris

[Deleted User]

Thanks for your recommendation for an update to the ODBC driver. I moved this topic to the "Ideas" section of our community so that this may be considered further in some of our future product releases. Please reference the new topic here: ODBC driver configuration change