{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transakcije v MariaDB/MySQL (transakcija A)\n",
    "\n",
    "### Pomožni elementi "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pyODBC\n",
    "import pyodbc\n",
    "try:\n",
    "    cn1.close()\n",
    "except:\n",
    "    pass\n",
    "\n",
    "# MariaDB/MySQL\n",
    "conn = \"DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=sandbox;UID=tup;PWD=tupvaje\"\n",
    "cn1 = pyodbc.connect(conn, autocommit=False)\n",
    "c1=cn1.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Izpis rezultatov poizvedbe (pomožna funkcija)\n",
    "def tabela(rez):\n",
    "  try:\n",
    "    # Glava\n",
    "    for g in rez.description:\n",
    "        print(g[0],end=\"\\t\")\n",
    "    print(\"\\n\"+\"-\"*31)\n",
    "    # Vsebina\n",
    "    for r in rez.fetchall():\n",
    "        for a in r:\n",
    "            print(a,end=\"\\t\")\n",
    "        print()    \n",
    "    # Število vrstic\n",
    "    print(\"Vseh vrstic je\", rez.rowcount)\n",
    "  except Exception(e):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s pomožnimi elementi transakcije B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A1: nastavi transakcijske parametre za vse nadaljnje nove transakcije"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Timeout ob predolgem zaklepanju\n",
    "c1.execute(\"SET SESSION innodb_lock_wait_timeout = 5\") # Čas v sekundah\n",
    "# Preizkusite različne stopnje izolacije\n",
    "c1.execute(\"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\") \n",
    "# Začnemo novo transakcijo\n",
    "c1.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A2: kreiramo in napolnimo testno tabelo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1.execute(\"DROP TABLE IF EXISTS rezervacija\")\n",
    "c1.execute(\"DROP TABLE IF EXISTS jadralec\") \n",
    "c1.execute(\"\"\"CREATE TABLE jadralec AS \n",
    "              SELECT * from tup.jadralec\"\"\")\n",
    "c1.execute(\"ALTER TABLE jadralec ADD PRIMARY KEY(jid)\")\n",
    "#c1.execute(\"ALTER TABLE jadralec ENGINE MYISAM\") # Privzeto: InnoDB\n",
    "c1.commit();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A3: izpis vsebine tabele pred spremembo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "jid\time\trating\tstarost\t\n",
      "-------------------------------\n",
      "22\tDarko\t7\t45.0\t\n",
      "29\tBorut\t1\t33.0\t\n",
      "31\tLojze\t8\t55.5\t\n",
      "32\tAndrej\t8\t25.5\t\n",
      "58\tRajko\t10\t35.0\t\n",
      "64\tHenrik\t7\t35.0\t\n",
      "71\tZdravko\t10\t16.0\t\n",
      "74\tHenrik\t9\t35.0\t\n",
      "85\tAnze\t3\t25.5\t\n",
      "95\tBine\t3\t63.5\t\n",
      "Vseh vrstic je 10\n"
     ]
    }
   ],
   "source": [
    "c1.execute(\"SELECT * FROM jadralec\")\n",
    "tabela(c1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s korakom B1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------------------------\n",
    "# <font color='green'>Branje neobstoječega podatka (dirty read)</font>\n",
    "\n",
    "- stopnja izolacije mora biti vsaj `READ COMMITTED`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A4: sprememba tabele"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    c1.execute(\"UPDATE jadralec  \\\n",
    "                SET rating = 222  \\\n",
    "                WHERE jid = 29\")\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A5: izpis vsebine tabele po spremembi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj na koraku B5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A6: razveljavi spremembe in ponovno izpiši"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "jid\time\trating\tstarost\t\n",
      "-------------------------------\n",
      "22\tDarko\t7\t45.0\t\n",
      "29\tBorut\t1\t33.0\t\n",
      "31\tLojze\t8\t55.5\t\n",
      "32\tAndrej\t8\t25.5\t\n",
      "58\tRajko\t10\t35.0\t\n",
      "64\tHenrik\t7\t35.0\t\n",
      "71\tZdravko\t10\t16.0\t\n",
      "74\tHenrik\t9\t35.0\t\n",
      "85\tAnze\t3\t25.5\t\n",
      "95\tBine\t3\t63.5\t\n",
      "Vseh vrstic je 10\n"
     ]
    }
   ],
   "source": [
    "c1.rollback()\n",
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='green'>Izgubljeno ažuriranje</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A7: začetek nove transakcije in izpis ratinga"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    }
   ],
   "source": [
    "c1.rollback()\n",
    "c1.execute(\"\"\" SELECT rating  \n",
    "               FROM jadralec\n",
    "               WHERE jid = 29\"\"\")\n",
    "rating = c1.fetchone()[0]\n",
    "print (rating)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s korakom B4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A8: sprememba ratinga"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyodbc.Cursor at 0x21b88ae6db0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.execute(\"UPDATE jadralec  \\\n",
    "            SET rating = ?  \\\n",
    "            WHERE jid = 29\", rating + 100)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s korakom B5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A9: potrjevanje sprememb in izpis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1.commit()\n",
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "------\n",
    "# <font color='green'>Neponovljivo branje (`non-repeatable read`)</font>\n",
    "- Potrebuje stopnjo izolacije najmanj `REPEATABLE READ` \n",
    "- Alernativno: `SELECT ...  LOCK IN SHARE MODE` na kateri koli stopnji izolacije"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A10: začetek transakcije in prvi izpis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1.commit()\n",
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s korakom B7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A11: nadaljevanje transakcije in drugi izpis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "---------\n",
    "# <font color='green'>Fantomsko branje (`phantom read`)</font>\n",
    "## Potrebujemo `SERIALIZABLE`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A12: začetek transakcije in prvi izpis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1.commit()\n",
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nadaljuj s korakom B8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A13: nadaljevanje transakcije in drugi izpis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Če je stopnja izolacije nižja od `SERIALIZABLE` pride do pojava fantomske vrstice."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "# <font color='green'>Mrtva zanka<font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Korak A14: prva sprememba in zaklepanje"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1.commit()\n",
    "c1.execute(\"SET innodb_lock_wait_timeout = 500\") # Daljši timeout samo za trenutno transakcijo\n",
    "c1.execute(\"UPDATE jadralec  \\\n",
    "            SET rating = ?  \\\n",
    "            WHERE jid = 22\", 122)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Nadaljuj s korakom B9.\n",
    "## Korak A15: druga sprememba in zaklepanje"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "c1.execute(\"UPDATE jadralec  \\\n",
    "            SET rating = ?  \\\n",
    "            WHERE jid = 29\", 129)\n",
    "c1.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Čakamo na transakcijo B, dokler se ta ne prekine zaradi pojava mrtve zanke.\n",
    "## Nadaljuj s korakom B10."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korak A16: izpis "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabela(c1.execute(\"SELECT * FROM jadralec\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}