locking - Redshift - enforcing autocommit

Question

Is there a Redshift cluster option to enforce autocommit=True for all connections? I.e. not to allow to establish a connection / run a query unless the setting was set.

We're having a lot of locking issues because some SQL tools have autocommit=False by default.

question from:https://stackoverflow.com/questions/65851945/redshift-enforcing-autocommit

Answer 1

I know of no such option and you likely wouldn't want one. The down side of autocommit is that all statements are their own transaction and must be processed through the commit queue. The commit queue is a singular resource in the cluster and can be overloaded if you have too many commits. ETL flows generally require performing multiple statements within a transaction to maintain database consistency.

With that said I feel your pain. Large poorly trained database users can cause all sorts of locking issues. Standardizing workbenches and their configuration can help but there will always be a few that have to use their own. Creating daemons that look for idle connections and close them also can help. Education is also key. The most important action is to rationalize your ETL flows to not be blocked by read locks. Some statements, like drop, will be blocked as long as a read lock is on the table but truncate can proceed with such locks. So drop-and-swap is not a good choice for tables that are used for queries by general users.

Sometimes I also wish there was a way to put certain users into autocommit along with read-only.