The set dml_logging command affects the logging mode only in the current session, and only for DML statements on tables owned by the session’s user. This allows DML commands to execute concurrently from multiple transactions, where full logging is employed in one session executing concurrently with minimal logging from another session. Any rollback in the session executing with full logging is undone, whereas the changes are not undone in a rollback from the other session.
A common example of such a usage is the execution of a transactional system, running small transactions concurrently with large batch updates or deletes in minimally logged mode. Any errors in either session do not affect the transactional consistency of the other session. In the example below, the OLTP transaction runs with full logging, enabling complete recoverability, whereas the batch operation runs from another session with minimal logging.
Figure 3-1: Concurrent execution of transactions with different logging modes
