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

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

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

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