What are some examples of transaction management in SQL?

What are some examples of transaction management in SQL?

Transaction management in SQL involves ensuring data integrity through operations like COMMIT, ROLLBACK, and SAVEPOINT. For example, if you are transferring money between accounts, you would use a transaction to ensure that both the debit and credit operations succeed or fail together.

Can you give me a specific SQL example?

Sure! Here's a simple example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

This transaction deducts $100 from account 1 and adds it to account 2. If any part fails, you can ROLLBACK to maintain data integrity.

What happens if one of the updates fails?

If one of the updates fails, the entire transaction can be rolled back using the ROLLBACK command. This means that any changes made during the transaction will be undone, ensuring that the database remains in a consistent state.

Are there any other transaction management commands I should know?

Yes, besides COMMIT and ROLLBACK, you should also know about SAVEPOINT, which allows you to set a point within a transaction to which you can later roll back without affecting the entire transaction.