Redshift stored procedure transaction keeps state after commit that leads to ERROR 1023 Serializable isolation violation

0

This stored procedure (excerpt) reliably generates ERROR: 1023 DETAIL: Serializable isolation violation on table tbl_process!

If only a single invocation occurs (Fast or Slow) the process always completes successfully inserting two rows (including updating the first)! However if two overlapping invocations occur with the first being a "Slow" process followed (before completing) by a "Fast" process - then the "Fast" process always completes successfully whilst the "Slow" process generates an exception (always mentioning 3 transactions forming the cycle).

Based on Redshift documentation I understood the BEGIN LOCK COMMIT END construct should prevent this! It appears the scalar var_process_start used in the UPDATE statement has retained some state from Transaction #1 that causes the Transaction #2 to fail - but only if any other processes transactions have been committed to the table. Note that the LOCK or INSERT in Transaction #2 does not fail - only the UPDATE.

How can this exception be avoided?

	DECLARE	var_process_start		INTEGER  ;
	BEGIN
		BEGIN	-- TRANSACTION #1
			LOCK TABLE tbl_process ;
			INSERT
			INTO	tbl_process
			(	process_user
			,	process_pid
			,	start_tstp
			)
			VALUES
			(	CURRENT_USER
			,	PG_BACKEND_PID()
			,	GETDATE()
			) ;
			-- Collect the tbl_process identity value for #1
			SELECT
			INTO	var_process_start
				MAX(process_event)
			FROM	tbl_process ;
			COMMIT ;
		END ;	-- TRANSACTION #1
		--
		/* Slow or Fast running processing here */
		/* Sets var_start_tstp and var_finish_tstp values */
		--
		BEGIN	-- TRANSACTION #2
			-- Avoid serializable isolation violations and deadlocks
			LOCK TABLE tbl_process ;
			-- Commit new process entry #2
			INSERT
			INTO	tbl_process
			(	process_user
			,	process_pid
			,	start_tstp
			,	finish_tstp
			)
			VALUES
			(	CURRENT_USER
			,	PG_BACKEND_PID()
			,	var_start_tstp
			,	var_finish_tstp
			) ;
			IF var_process_start > 0 THEN
				-- Record the end of this processing run on #1
				UPDATE	tbl_process
				SET	finish_tstp	= GETDATE()
				WHERE	process_event	= var_process_start ;
				-- ^^^ this statement generates serialization isolation violation!
			END IF ;
			-- Finished - commit all work to database
			COMMIT ;
		END ;	-- TRANSACTION #2

  • 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.

asked 2 years ago1452 views
1 Answer
0

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

AWS
SUPPORT ENGINEER
answered 2 years ago
  • 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 !!!

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions