Hello, we seem to have discovered this limitation where only one user-specified "transaction" can be in progress at a time. Even if they operate on different tables. That is, if a job started with a Begin component, and it is in progress, and it operates on table X ... then any other job (being run in the same environment) that starts with a Begin component must wait until the first one finishes (even if the other job does not operate on table X). Is that the expected behavior? And if so, why?
I'm no Redshift DBA, and I've been trying to research this question on the Web, but I can't figure it out. I read things about locks; and how each transaction operates on a snapshot of the database; and "serializable isolation."
I also read here that "Concurrent transactions are invisible to each other; they cannot detect each other's changes. Each concurrent transaction will create a snapshot of the database at the beginning of the transaction." To me, this seems to imply that Redshift can have multiple transactions executing simultaneously. I'm not sure. But does Matillion prevent us from doing so?
if you have some common tables to be updated in both different jobs then this situation will happens . For that check whether there is any common table like audit table update or anything else .
We also faced this type of issues while using begin and commit then we analyzed some points which is to be used while implementing begin and commit
Keep the begin and commit as shortly as possible (should not keep from the beginning of the job just keep where the update actions are held or extract actions are held)
avoid using multiple begin and commit in same job
check for the common tables in concurrent jobs
don't apply begin and commit in concurrent jobs which update the same table (if any jobs updating the same table concurrently then the second job should wait until the first job get finished)
select * from svv_transactions; use this to check deadlock
I think this response misses the mark David. OP is right... any RDBMS worth its salt will support multiple concurrent explicit transactions. It's as simple as naming the transaction when you begin it. Matillion doesn't allow us to take advantage of this. I'm getting the same concurrency errors as Kevin.
Yes, the things you mention are great practices when implementing transactions. That doesn't help me cut down my run times by running logically independent jobs concurrently.