Netezza Transactions BEGIN, COMMIT, ROLLBACK - learn

Netezza transactions can be summarized using the following :

Transactions start with BEGIN WORK.
Transactions are validated with COMMIT WORK.
Transactions are canceled with ROLLBACK WORK.
Statements executed outside of a transaction are automatically committed.
If an SQL error occurs in one of the statements executed inside the transaction, Netezza cancels the entire transaction
DDL statements can be executed (and canceled) in transactions.
A transaction must only contain INSERTs if you want concurrent processes to insert rows at the same time (UPDATEs/DELETEs lock the whole table).
Only the SERIALIZABLE isolation level is implemented by Netezza.

BEGIN WORK INSERT INTO existingtable ( 1, 'abc' ) -- successful sql SELECT FROM unexisting WHERE key = 123 -- unexisting table = sql error COMMIT WORK

The table will remain empty after executing this piece of code, because the server will rollback the whole transaction.

Solution

Since Netezza is not designed for OLTP applications, review any code doing complex data modifications to ensure it doesn’t error out. To get the same behavior in case of error when connected to a different database than Netezza, you must issue a ROLLBACK to cancel all the SQL statements that succeeded in the transaction, for example with a TRY/CATCH block.

TRY BEGIN WORK ... COMMIT WORK CATCH ROLLBACK WORK END TRY

BEGIN/COMMIT/ROLLBACK commands takes the optional input parameters, WORK or TRANSACTION – and have no effect on the transactions.

RollBack

Four Isolation Levels

Isolcation Level	Uncommited Data	Non Repeatable Data	Phantom Data
Uncommitted	Y	Y	Y
Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

Isolation levels

Uncommitted Data: Transaction can see data from another transaction which is still not committed

Non Repeatable Data: When a transaction tries to read the same data, the data is changed by another transaction but committed after the previous read.

Phantom Data: When a transaction tries to read the same data, the data previously read is not changed but new rows has been added which satisfies the previous query criteria i.e. new rows has been added to the data rows.

Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

👨‍💻 All of my projects are available at https://github.com/akrish1982
💬 Ask me about Data engineering, SQL, Databases, Data pipelines, Data infrastructure
📄 My work history: https://www.linkedin.com/in/ananthtirumanur/
⚡ Fun fact: Marathoner & Casual Birding enthusiast

Connect with me:

Twitter: @akrish82
LinkedIn: https://linkedin.com/in/ananthtirumanur/

My Resources:

LinkedIn Newsletter: Data Engineering with AWS
Udemy Course: AWS Certified Data Engineer Associate Practice Test
Python Crash Course: Python Crash Course on Gumroad

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite