Prepare statement

 -- Step 1: Prepare an INSERT statement to insert a user into the users table

PREPARE insert_stmt FROM 'INSERT INTO users (name, email) VALUES (?, ?)';

-- Step 2: Set values for placeholders

SET @name = 'John Doe', @email = 'john.doe@example.com';

-- Step 3: Execute the prepared INSERT statement with the provided values

EXECUTE insert_stmt USING @name, @email;

-- Step 4: Deallocate the prepared INSERT statement

DEALLOCATE PREPARE insert_stmt;

-- Step 5: Prepare a SELECT statement to retrieve user by ID

PREPARE select_stmt FROM 'SELECT id, name, email FROM users WHERE id = ?';

-- Step 6: Set the value for the ID of the user to retrieve

SET @id = 1;

-- Step 7: Execute the SELECT statement with the ID

EXECUTE select_stmt USING @id;

-- Step 8: Deallocate the prepared SELECT statement

DEALLOCATE PREPARE select_stmt;

MySQL Transactions

 In MySQL, transactions allow you to group multiple SQL statements into a single unit of work. This ensures that the changes made by those statements are executed together, and if something goes wrong, the transaction can be rolled back to maintain data integrity. Transactions are especially useful when you need to perform a series of operations that should either all succeed or all fail.

Basic Commands for MySQL Transactions

1. Start a Transaction
   To start a transaction, you use the START TRANSACTION or BEGIN command.

START TRANSACTION;

2 Commit a Transaction
To save all the changes made during the transaction, you use the COMMIT command. Once a transaction is committed, the changes become permanent.

COMMIT;

3 Rollback a Transaction
If something goes wrong during the transaction (e.g., a constraint violation), you can use the ROLLBACK command to undo all the changes made since the transaction started.

ROLLBACK;

4 Savepoints
A savepoint allows you to create a point within a transaction to which you can roll back without affecting the entire transaction.

SAVEPOINT savepoint_name;

If you want to roll back to a specific savepoint:

ROLLBACK TO SAVEPOINT savepoint_name;

5 Autocommit Mode
By default, MySQL runs in autocommit mode, meaning every SQL statement is treated as a transaction. If you want to disable autocommit for your session (so you can manually manage transactions), use the following command:

SET autocommit = 0;

To re-enable autocommit, use:

SET autocommit = 1;

Example of Using Transactions in MySQL

START TRANSACTION;

-- Insert data into a table

INSERT INTO accounts (account_id, balance) VALUES (1, 500);

INSERT INTO accounts (account_id, balance) VALUES (2, 300);

-- Simulate an error (e.g., a constraint violation)

-- For example, inserting an invalid value

-- INSERT INTO accounts (account_id, balance) VALUES (NULL, 'invalid_balance');

-- If no error occurs, commit the transaction

COMMIT;

In the above example:

1. We start a transaction with START TRANSACTION.
2. We perform some INSERT operations.
3. If everything is successful, we use COMMIT to save the changes to the database.
4. If an error occurs during the transaction (e.g., inserting invalid data), we could roll back the transaction with ROLLBACK to ensure the database stays in a consistent state.

Isolation Levels in MySQL

MySQL supports different transaction isolation levels, which control the visibility of changes made within a transaction to other transactions. The isolation level can be set as follows:

Here are the available isolation levels:

1. READ UNCOMMITTED: Transactions can see uncommitted changes made by other transactions.
2. READ COMMITTED: Transactions can only see committed changes from other transactions.
3. REPEATABLE READ: Ensures that if a transaction reads a value, it will see the same value if it reads it again later. This is the default isolation level in MySQL.
4. SERIALIZABLE: The highest isolation level. It ensures no other transactions can read, write, or even lock the same rows.

Example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Key Points to Remember

• Atomicity: A transaction ensures that all the changes are applied or none at all. If a transaction is rolled back, none of the operations will take effect.
• Consistency: Transactions bring the database from one valid state to another, ensuring integrity.
• Isolation: Transactions are isolated from each other. Depending on the isolation level, the changes in one transaction may or may not be visible to others.
• Durability: Once a transaction is committed, the changes are permanent, even if the system crashes.

Conclusion

Transactions in MySQL are essential for maintaining data integrity and consistency, especially in systems where multiple operations need to be executed together. By using START TRANSACTION, COMMIT, and ROLLBACK, you can ensure that your database operations are reliable and robust.