UNDO processing explained

[Syntas_error]

I'm trying to wrap my head around what's being said about "UNDO-processing" in the SAS advanced programmer prep guide, but strangely, the term is never defined.  

Questions:

1. How is "UNDO-processing"/"UNDO-operations" defined, what happens during this processing/this operation?

2. When exacly can an UNDO operation "be done reliably"?

3. What exacly is the difference between UNDO_POLICY=REQUIRED  and UNDO_POLICY=OPTIONAL? 

This is the relevant passage from the book:

UNDO_POLICY=REQUIRED

PROC SQL performs UNDO processing for INSERT and UPDATE statements. If the UNDO operation cannot be done reliably, PROC SQL does not execute the statement and issues an ERROR message.
This is the PROC SQL default.

UNDO_POLICY=NONE

PROC SQL skips records that cannot be inserted or updated, and writes a warning message to the SAS log similar to that written by PROC APPEND. Any data that meets the integrity constraints is added or updated.

UNDO_POLICY=OPTIONAL
PROC SQL performs UNDO processing if it can be done reliably. If the UNDO cannot be done reliably, then no UNDO processing is attempted.This action is a combination of REQUIRED and NONE. If UNDO can be done reliably, then it is done, and PROC SQL proceeds as if UNDO_POLICY=REQUIRED is in effect. Otherwise, it proceeds as if UNDO_POLICY=NONE was specified.

[tsap]

UNDO_POLICY=NONE|OPTIONAL|REQUIRED

specifies how PROC SQL handles updated data if errors occur while you are updating data. You can use UNDO_POLICY= to control whether your changes will be permanent:

NONE

keeps any updates or inserts.

OPTIONAL

reverses any updates or inserts that it can reverse reliably.

REQUIRED

reverses all inserts or updates that have been done to the point of the error. In some cases, the UNDO operation cannot be done reliably. For example, when a program uses a SAS/ACCESS view, it might not be able to reverse the effects of the INSERT and UPDATE statements without reversing the effects of other changes at the same time. In that case, PROC SQL issues an error message and does not execute the statement. Also, when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, you cannot reliably reverse your changes.

This option can enable other users to update newly inserted rows. If an error occurs during the insert, then PROC SQL can delete a record that another user updated. In that case, the statement is not executed, and an error message is issued.

Default:	REQUIRED
Tip:	If you are updating a data set using the SPD Engine, you can significantly improve processing performance by setting UNDO_POLICY=NONE. However, ensure that NONE is an appropriate setting for your application.
Tip:	Alternatively, you can set the SQLUNDOPOLICY system option. The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the PROC SQL UNDO_POLICY= option is set. The value of the UNDO_POLICY= option takes precedence over the SQLUNDOPOLICY= system option. The RESET statement can also be used to set or reset the UNDO_POLICY= option. However, changing the value of the UNDO_POLICY= option does not change the value of the SQLUNDOPOLICY= system option. After the procedure completes, the undo policy reverts to the value of the SQLUNDOPOLICY= system option. For more information, see the SQLUNDOPOLICY system option in the SAS Language Reference: Dictionary.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473669.htm

Related System Option Information:

SQLUNDOPOLICY= System Option

Specifies whether the SQL procedure keeps or discards updated data if errors occur while the data is being updated.

Valid in:	configuration file, SAS invocation, Options statement
Category:	Files: SAS Files
	System administration: SQL
PROC OPTIONS GROUP=	SASFILES
	SQL

Syntax

Syntax Description

Details

See Also

Syntax

SQLUNDOPOLICY=NONE | OPTIONAL | REQUIRED

Syntax Description

NONE

specifies to keep changes that are made by the INSERT and UPDATE statements.

OPTIONAL

specifies to reverse changes that are made by the INSERT and UPDATE statements as long as reversing the changes is reliable.

REQUIRED

specifies to undo all changes that are made by the INSERT and UPDATE statements, up to the point of the error. This is the default.

CAUTION:

Some UNDO operations cannot reliably reverse changes.

In some situations, reversing the effects of the INSERT and UPDATE statements cannot be done reliably. When operations cannot be reversed, the SQL procedure issues an error message and does not execute the statement. For example, when a program uses a SAS/ACCESS view, or when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, changes cannot be reliably reversed.  

CAUTION:

Some UNDO operations might not reverse changes.

In situations where multiple transactions are made to the same record, PROC SQL might not reverse a change; it will issue an error message instead. For example, if an error occurs during an insert, PROC SQL can delete a record that another user updated. In that case, the UNDO statement is not executed, and an error message is issued.  

Details

The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the UNDO_POLICY option in the PROC SQL statement is set. The value of the UNDO_POLICY option takes precedence over the SQLUNDOPOLICY= system option. The RESET statement can also be used to set or reset the UNDO_POLICY option. However, changing the value of the UNDO_POLICY option does not change the value of the SQLUNDOPOLICY= system option. Once the procedure completes, the undo policy reverts to the value of the SQLUNDOPOLICY= system option.

If you are updating a data set using the SPD Engine, you can significantly improve processing performance by setting SQLUNDOPOLICY=NONE. However, ensure that NONE is an appropriate setting for your application.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003157487.htm