8. Transaction Control Language (TCL).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c15722e1-3928-4502-8dab-f2b7b9eeae7c",
   "metadata": {},
   "source": [
    "# 8. Transaction Control Language"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c05e5b9e-083c-4d8a-a465-7c66812b9c41",
   "metadata": {},
   "source": [
    "The Transaction Control Language manages the changes in the data which are made by the DML operations. The alteration made by the DML commands such as UPDATE, INSERT or DELETE is not permanent and these changes can be canceled before the present session gets closed. To control the changes and processing of data, TCL is used simultaneously with the Data Manipulation Language. As we perform many operations on the data, the database might become inconsistent between the transactions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d6bd02-c63b-4e4b-a5d1-fb3f0947eb0f",
   "metadata": {},
   "source": [
    "So the Transaction Control Language `(TCL)` is used to maintain consistency and manage the transaction in a database. After the commit is performed the database state is changed from one to another consistent state."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f23bf13d-a8fa-4649-ba60-a6c46313bafc",
   "metadata": {},
   "source": [
    "## COMMIT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "457e270a-d8be-497d-8bb2-810c2ddfffb7",
   "metadata": {},
   "source": [
    "`COMMIT` is used to make all the changes performed within a transaction permanent. It marks the successful completion of a transaction, and the changes are saved to the database. If a transaction is not explicitly committed, it is automatically rolled back when it reaches its logical conclusion or in the case of an error."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0da08ee-7b71-4670-a584-b8190ef6f8de",
   "metadata": {},
   "source": [
    "```sql\n",
    "COMMIT;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ff9e8e-1954-426d-b9d4-503a3f89b955",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "720b9e8f-b2ad-4da6-a9bd-c9a2325685b2",
   "metadata": {},
   "source": [
    "```SQL\n",
    "ALTER TABLE customers\n",
    "ADD COLUMN salting varbinary(16);\n",
    "\n",
    "\n",
    "-- Generate a unique salt for each user\n",
    "UPDATE customers SET salting= UNHEX(SUBSTRING(SHA2(UUID(), 256), 1, 32));\n",
    "\n",
    "\n",
    "-- Combine salt and existing password and hash them\n",
    "UPDATE customers SET password = SHA2(CONCAT(salting, password), 256);\n",
    "\n",
    "-- Commit the changes\n",
    "COMMIT;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "274e4175-0cf5-459e-b900-f6b920d89e69",
   "metadata": {},
   "source": [
    "## ROLLBACK"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffbc6fd5-8638-4727-a0a2-cbd6a219d3bf",
   "metadata": {},
   "source": [
    "This command is used to undo all the changes made during a transaction, canceling the entire transaction. It is typically used in response to errors or exceptional situations that require a transaction to be canceled."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "039b2873-6141-4af1-b2b4-f905fdf4226b",
   "metadata": {},
   "source": [
    "```SQL\n",
    "ROLLBACK;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c046c4a-e52c-4545-8b00-89862e1ba398",
   "metadata": {},
   "source": [
    "Example:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbc19327-de4c-424b-b2a4-4f4037a5c104",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE customers -- the transaction\n",
    "SET first_name = 'Elisa'\n",
    "WHERE customer_id = 254;\n",
    "\n",
    "ROLLBACK; -- cancelling the transaction, so the database returns to it`s previous state\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9974321-25a4-4313-ba4e-e15c6b778682",
   "metadata": {},
   "source": [
    "**STRUCTURE:**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "228c3f70-c8f1-41de-8d85-0feec130c8d3",
   "metadata": {},
   "source": [
    "```SQL\n",
    "BEGIN TRANSACTION; -- start a transaction\n",
    "\n",
    "-- Body of the transaction\n",
    "\n",
    "COMMIT; -- in case changes that were made are as  planned and you are willing to apply them to the database\n",
    "-- or \n",
    "ROLLBACK; -- in case changes that were made are not satisfactory and you want to cancel them and return to the previous state\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}