- Newest
- Most votes
- Most comments
The error seems to be occurring when there is concurrent invocation of the same procedure ( insert and update). You can take a look at how and when Lock command will be effective within a stored procedure call. ->https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-transaction-management.html The best way to troubleshoot this issue is to find out which statements might be running into conflict. Using the transaction ids (present on the error), you can query the stl_tr_conflict ->https://docs.aws.amazon.com/redshift/latest/dg/r_STL_TR_CONFLICT.html , to view the transactions which conflicted and query svl_statementtext to see what statements ran in those transactions and where the conflict occured->https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STATEMENTTEXT.html
Thanks for the tips - as I am not a superuser STL_TR_CONFLICT is hard to access but will try to get some info from SVL_STATEMENTTEXT.
To be clear re sequence of events - the "Slow" process starts first and completes Transaction #1 but has not started Transaction #2 when the "Fast" process commences. The "Slow" process has no lock on the table and the only information retained is the scalar variable holding the identity value. The "Fast" process has concluded its psql session well before the "Slow" process starts Transaction #2 where the LOCK is granted, INSERT succeeds but UPDATE fails !!!
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated 21 days ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated a month ago
Bumping as review of SVL_STATEMENTTEXT indicates Redshift IMO is not ACID compliant re Isolation!
The failing UPDATE is not commenced until well after another BEGIN UPDATE COMMIT on the subject table has concluded.
However it appears because the table was referenced in prior SELECT statements Redshift throws a SIV on the second UPDATE when it detects "something has changed" since the session was commenced.
It appears Redshift either does not record sufficient granularity, or is misinterpreting the context, such that it is unable determine that the statements are in fact isolated.