{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "71d090db",
   "metadata": {},
   "source": [
    "## Lecture 26\n",
    "\n",
    "### Announcements\n",
    "\n",
    "- This is our last class!\n",
    "\n",
    "\n",
    "### SQL Isolation Levels\n",
    "\n",
    "- read uncommitted;\n",
    "  - Not supported in Postgresql\n",
    "- read committed;\n",
    "  - Does not allow read of uncommitted values, but the transaction may read the same value twice and get different results\n",
    "- repeatable read ;\n",
    "  - Does not allow the same read producing different results, but it allows phantom updates (changes that are not directly read but impact the query results)\n",
    "- serializable ;\n",
    "  - Does not allow phantom updates either.\n",
    "\n",
    "\n",
    "```\n",
    "-- T1:\n",
    "begin transaction ;\n",
    "set transaction isolation level read uncommitted;\n",
    "   --not implemented\n",
    "set transaction isolation level read committed;\n",
    "set transaction isolation level repeatable read ;\n",
    "set transaction isolation level serializable ;\n",
    "\n",
    "commit ;\n",
    "end;\n",
    "\n",
    "--- T2\n",
    "begin transaction ;\n",
    "\n",
    "commit ;\n",
    "end;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8972b21d",
   "metadata": {},
   "source": [
    "## Implementing serializability\n",
    "\n",
    "Two main approaches:\n",
    "\n",
    "- Locking mechanism: 2 Phase locking\n",
    "- Optimistic mechanism: Multi-version control (check at commit time if a change should be allowed)\n",
    "\n",
    "\n",
    "### Two phase locking\n",
    "\n",
    "- Lock any item before a read or write operation\n",
    "  - If the lock is available, xact can get it and continue processing\n",
    "  - If the lock is not available, xact must wait until it becomes available\n",
    "  - Only a single transaction can lock an item at any point in time\n",
    "  \n",
    "- A transaction can get new locks if it is in the **growing phase**\n",
    "  - Transactions cannot release locks in the growing phase\n",
    "- A transaction can release locks if it is in the **shrinking phase**\n",
    "  - Transactions cannot get any more locks in the shrinking phase\n",
    "- As soon as a transaction in the growing phase releases a lock, it enters the shrinking phase.  \n",
    "\n",
    "\n",
    "- Two phase locking guarantees serializability!\n",
    "- If a transaction management system uses 2PL (two phase locking), then all the schedules produced by this system are guaranteed to be serializable.\n",
    "\n",
    "- Example:\n",
    "\n",
    "```\n",
    "  r1(x) r2(x) w1(x)\n",
    "  \n",
    "  Conflicts:\n",
    "  r1(x) w2(x)\n",
    "  r2(x) w1(x)\n",
    "```\n",
    "\n",
    "Not serializable, there is a cycle. \n",
    "\n",
    "If I were using 2PL, this schedule is not possible.\n",
    "\n",
    "\n",
    "```\n",
    "lock1_(x) r1(x) unlock1(x) lock2(x) r2(x) unlock2(x) XXXXXw1(x) \n",
    "                ^T1 enters                           Not possible\n",
    "                shrinking phase                      T1 in shrinking\n",
    "                and has no lock on x\n",
    "```  \n",
    "\n",
    "- 2PL guarantees serializability\n",
    "  - Problems may occur with deadlocks and lock granularity (for higher concurrency) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0faa10c9",
   "metadata": {},
   "source": [
    "### Locking with shared/exclusive locks\n",
    "\n",
    "- Two types of locks\n",
    "  - Read lock, shared lock (S lock)\n",
    "    - To read an item, transactions must obtain a shared lock (S)\n",
    "    - Multiple transactions can hold an S lock on the same item\n",
    "  - Write lock, exclusive lock (X lock)\n",
    "    - To write an item, transaction must obtain an exclusive lock (X)\n",
    "    - If T1 exclusively locks an item, no other transaction can hold any lock lock on this item\n",
    "  - If a transaction is the only one holding an S lock, then it can upgrade it to a write lock without unlocking (otherwise you will enter your shrinking phase)     \n",
    "    \n",
    "- These two schedules are possible under 2PL with two types of locks.\n",
    "\n",
    "```\n",
    "sl1(x) r1(x) xl1(x) w1(x)\n",
    "```\n",
    "\n",
    "```\n",
    "sl1(x) r1(x) sl2(x) r2(x) unlock2(x) xl1(x) w1(x)\n",
    "```\n",
    "\n",
    "- This transaction is not possible because T2 has entered shrinking phase when it unlocked x and not needs to lock x again to write x. Not possible!\n",
    "```\n",
    "sl1(x) r1(x) sl2(x) r2(x) unlock2(x) xl1(x) w1(x) unlock1(x)   ???? not possible w2(x)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c85f3dae",
   "metadata": {},
   "source": [
    "## DB Tuning with indexing\n",
    "\n",
    "- Queries and their cost ( Cost(Q) )\n",
    "\n",
    "- Target queries that are very costly or very frequntly asked or both\n",
    "\n",
    "- Workload:   Sum over all queries ( Cost(Q) * Frequency(Q) )"
   ]
  }
 ],
 "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.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}