Transactions and execution rights

BEGIN … END groups procedural statements. It does not itself start a database transaction. Use BEGIN TRANSACTION and finish with COMMIT or ROLLBACK when statements must share an explicit transaction.

Make failure observable

The order-processing tutorial declares forced_failure, starts a transaction, updates eligible orders, and raises that exception when its test flag is true. The matching handler rolls back and returns -1. Unexpected errors roll back and are rethrown with bare RAISE.

This distinguishes an intentional demonstration failure from an unexpected execution error. The worksheet queries the table after rollback so you can see that the attempted updates did not persist. It then calls the same procedure with the default FALSE flag and checks the committed rows.

Keep transaction ownership clear: start and finish a procedure's transaction in that procedure. Do not design an application around one procedure committing a caller's transaction, and do not use Continue on error as a substitute for rollback logic.

Repeated work and concurrent work

The demo changes only rows with status = 'new'. Its second successful call finds no eligible rows and returns zero. This illustrates repeatable state transitions for a single worker.

A repeated-call check does not establish exactly-once processing under concurrent workers. For a real dispatch or ingestion service, define a job identity, concurrency controls, retry policy, and audit record. The tutorial counts matching rows before updating; that count is a teaching result for the isolated fixture, not a concurrency-safe claim receipt.

The table reset at the top of the worksheet is for synthetic data. Production workflows should use explicit schema migrations and retain their operational records.

Use the caller's permissions

The procedures explicitly declare EXECUTE AS CALLER. The caller needs the relevant object privileges, including EXECUTE on a stored procedure and the table operations performed by its body. Sharing the worksheet shares SQL text; it does not provision these grants or elevate execution.

Owner-rights execution requires service-side enablement and an authorized identity exchange, both managed by Gnok. It is disabled by default. The order-processing tutorial does not require this setting. See the stored-procedure reference before deploying an owner-rights procedure.

Adapt the pattern

For an ingestion workflow, replace the order update with a staging-table check followed by a publication step. For a scheduled analytical refresh, validate the input partition before updating an output table. In both cases, decide which failures should be handled locally and which must propagate to the scheduler or client.