Explanation of SQL transactions and ACID properties with examples of COMMIT and ROLLBACK for database consistency

6.What is SAVEPOINT in SQL and how does it work?
A SAVEPOINT is a point created within a transaction that you may roll back to later without rolling back the entire transaction. It supports partial rollbacks and therefore makes transactions more flexible.
Example Query:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_first_update;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
ROLLBACK TO after_first_update;
COMMIT;
In this case, if the third update of the transaction fails, you can roll back to the SAVEPOINT and retain the first update.

7.What is the concept of an isolation level in SQL transactions? Why is this relevant?
Isolation level means the integrity of the transactions if they are running in a multi-processing environment. It means visibility of the data modifications carried out by one transaction to the others. Common Isolation Levels:
•Read Uncommitted: Allows dirty reads.
•Read Committed: Prevents dirty reads but allows non-repeatable reads.
•Repeatable Read: Prevents dirty and non-repeatable reads but allows phantom reads.
•Serializable: Provides the highest isolation, ensuring transactions are executed serially.
Example Query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
The choice of isolation level impacts performance and concurrency in multi-user environments.

8.What is the difference between LOCK and ISOLATION in SQL transactions?
Both LOCK and ISOLATION pertain to how data is accessed and modified as transactions occur concurrently though on two different concepts:
LOCK : Ensures that a row or table is locked up to the point when a current transaction is committed against some other transaction from updating it.
ISOLATION: Determines whether the data accessed by concurrent transactions should be seen by the other, how the modifications of one transaction should be visible to another.
The following is an example query with locking:
BEGIN
TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Update or other operations
COMMIT;
In the example above, the statement FOR UPDATE locks the selected rows so that no other transactions can change them.

9.How does the Durability ACID property work in SQL?
Durability guarantees that once a transaction has been committed, the modifications are persisted even in case of system failure or crash. This is usually implemented through transaction logs and database backups so that no data committed will be lost.
Example Scenario: In case a system crashes immediately after a COMMIT operation, the database ensures that the committed data is still available upon recovery by using transaction logs to replay the committed changes.

10.What are good practices for ensuring transactional consistency and reliability in SQL?
To achieve consistency and reliability, the following can be used:
1.Use proper isolation levels of transaction depending on the scenario.
2.Implement error handling mechanism, where any errors occurring in a transaction are caught and rolled back appropriately.
3.Use COMMIT and ROLLBACK properly and do not leave transactions open for extended periods to avoid lock conditions.
4.Consider using SAVEPOINT for partial rollbacks of large transactions.
Example Query (Error Handling):
BEGIN TRANSACTION;
BEGIN TRY
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
-- Handle error
END CATCH;
This ensures that any errors during the transaction result in a rollback, preserving data integrity.