{ "metadata": { "name": "", "signature": "sha256:eb3220007d993b7ffd44e2011c72b822d501901a291697b85dde94f68085a610" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Transactions 1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
Note a small example database is used here. I envisage replacing this by using a dataset that has been introduced previously" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "need some reference for the commands eg like http://www.tutorialspoint.com/postgresql/postgresql_python.htm" ] }, { "cell_type": "code", "collapsed": false, "input": [ "import pandas as pd" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 1 }, { "cell_type": "code", "collapsed": false, "input": [ "import pandas.io.sql as psql" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 2 }, { "cell_type": "code", "collapsed": false, "input": [ "import psycopg2 as pg" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 3 }, { "cell_type": "code", "collapsed": false, "input": [ "conn = pg.connect(\"dbname='postgres' host='localhost' user='test' password='test' \")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 4 }, { "cell_type": "code", "collapsed": false, "input": [ "c = conn.cursor()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 5 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute('SELECT version()') " ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 6 }, { "cell_type": "code", "collapsed": false, "input": [ "ver = c.fetchone()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 7 }, { "cell_type": "code", "collapsed": false, "input": [ "print(ver)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "('PostgreSQL 9.1.11 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.3-1ubuntu1) 4.7.3, 32-bit',)\n" ] } ], "prompt_number": 8 }, { "cell_type": "markdown", "metadata": {}, "source": [ "
We are going to import the same dataset that we used in Unit xx and use it to explore how transactions work in PostgreSQL.\n", "Remember that the data in this dataset is consistent if ........\n", "Let us display some of the data. Run the following cells:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"DROP TABLE IF EXISTS cars\")\n", "c.execute(\"CREATE TABLE cars(id INT PRIMARY KEY, name VARCHAR(20), price INT)\")\n", "c.execute(\"INSERT INTO cars VALUES(1,'Audi',52642)\")\n", "c.execute(\"INSERT INTO cars VALUES(2,'Mercedes',57127)\")\n", "c.execute(\"INSERT INTO cars VALUES(3,'Skoda',9000)\")\n", "c.execute(\"INSERT INTO cars VALUES(4,'Volvo',29000)\")\n", "c.execute(\"INSERT INTO cars VALUES(5,'Bentley',350000)\")\n", "c.execute(\"INSERT INTO cars VALUES(6,'Citroen',21000)\")\n", "c.execute(\"INSERT INTO cars VALUES(7,'Hummer',41400)\")\n", "c.execute(\"INSERT INTO cars VALUES(8,'Volkswagen',21600)\")\n", "conn.commit()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 9 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"SELECT * FROM cars\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 10 }, { "cell_type": "code", "collapsed": false, "input": [ "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 11 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"DROP TABLE IF EXISTS dealers\")\n", "c.execute(\"CREATE TABLE dealers(dealername VARCHAR(30), carid INT, town VARCHAR(15))\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 12 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"INSERT INTO dealers VALUES('FiveWays',1,'Leeds')\")\n", "c.execute(\"INSERT INTO dealers VALUES('Freds',1,'MK')\")\n", "c.execute(\"INSERT INTO dealers VALUES('Hartwell',4,'Lincoln')\")\n", "c.execute(\"INSERT INTO dealers VALUES('CarsRUs',4,'Chester')\")\n", "c.execute(\"INSERT INTO dealers VALUES('BigCars',5,'IOW')\")\n", "c.execute(\"INSERT INTO dealers VALUES('LittleCars', 7,'IOM')\")\n", "c.execute(\"INSERT INTO dealers VALUES('TheGarage',7,'York')\")\n", "c.execute(\"INSERT INTO dealers VALUES('VWCars',7,'Rhyl')\")\n", "conn.commit()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 13 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"SELECT * FROM dealers\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 14 }, { "cell_type": "code", "collapsed": false, "input": [ "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "('FiveWays', 1, 'Leeds')\n", "('Freds', 1, 'MK')\n", "('Hartwell', 4, 'Lincoln')\n", "('CarsRUs', 4, 'Chester')\n", "('BigCars', 5, 'IOW')\n", "('LittleCars', 7, 'IOM')\n", "('TheGarage', 7, 'York')\n", "('VWCars', 7, 'Rhyl')\n" ] } ], "prompt_number": 15 }, { "cell_type": "markdown", "metadata": {}, "source": [ "(Some of) the data that we are working with is displayed. Does it appear to be consistent according to the rules that were defined for this dataset?\n", "
Answer It is consistent because .....
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now run the code below. It will update some of the data." ] }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"UPDATE Cars SET price=53005 WHERE id=1\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 16 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"SELECT * FROM cars\")\n", "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 53005)\n" ] } ], "prompt_number": 17 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"UPDATE cars SET price=21059 WHERE id=6\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 18 }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"SELECT * FROM cars\")\n", "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 53005)\n", "(6, 'Citroen', 21059)\n" ] } ], "prompt_number": 19 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Is the data consistent? The data has not been committed. Now see what happens if we roll back." ] }, { "cell_type": "code", "collapsed": false, "input": [ "conn.rollback()\n", "c.execute(\"SELECT * FROM cars\")\n", "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 20 }, { "cell_type": "markdown", "metadata": {}, "source": [ "What has the rollback command done? Where was the beginning of the transaction?\n", "\n", "Is the data now in its previous consistent state? Make notes below about what you have observed." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
Answer It is in its previous consistent state because the output is the same as it was before the two updates.\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "What do you think will happen if the same record is upated twice and then rolled back? Run the following code to see if it behaves as you expected: " ] }, { "cell_type": "code", "collapsed": false, "input": [ "c.execute(\"UPDATE cars SET price=54009 WHERE id=1\")\n", "c.execute(\"UPDATE cars SET price=56650 WHERE id=1\")\n", "c.execute(\"SELECT * FROM cars\")\n", "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 56650)\n" ] } ], "prompt_number": 21 }, { "cell_type": "code", "collapsed": false, "input": [ "conn.rollback()\n", "c.execute(\"SELECT * FROM cars\")\n", "for row in c:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 22 }, { "cell_type": "markdown", "metadata": {}, "source": [ "
Is it possible to view the transaction log? - \n", "it would be interesting to see the log prior to a COMMIT or ROLLBACK and immediately after." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the following cell, code a transaction to change the name of the 'Fiveways' dealer to 'Hartwell' and to delete the record for 'LittleCars'. Include a commit at the end." ] }, { "cell_type": "code", "collapsed": false, "input": [], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 22 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Does your transaction have the ACID properties as described in section ....?\n", "
Answer It is atomic because all is executed when the commit happens, otherwise a rollback would cause none of it to be executed.\n", "It preserves consistency because .... It did not execute concurrently with any other transactions so it was isolated. The changes once they are committed are durable. PostgreSQL makes sure this is the case." ] }, { "cell_type": "heading", "level": 3, "metadata": {}, "source": [ "Now we are going to investigate what happens when two people connect to the database and each of them accesses the database." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Run the following cell to create two connections, one for Alice and one for Bob." ] }, { "cell_type": "code", "collapsed": false, "input": [ "aliceConnection = pg.connect(\"dbname='postgres' host='localhost' user='test' password='test' \")\n", "ac=aliceConnection.cursor()\n", "bobConnection = pg.connect(\"dbname='postgres' host='localhost' user='test' password='test' \")\n", "bc=bobConnection.cursor()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 23 }, { "cell_type": "markdown", "metadata": {}, "source": [ "They should both be able to read the data. Try it:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "ac.execute(\"SELECT * FROM cars\")\n", "for row in ac:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 24 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars\")\n", "for row in bc:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 25 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now Alice updates a price. Run the following:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "ac.execute(\"UPDATE cars SET price=56650 WHERE id=1\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 26 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Alice reads the resulting data: " ] }, { "cell_type": "code", "collapsed": false, "input": [ "ac.execute(\"SELECT * FROM cars\")\n", "for row in ac:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 56650)\n" ] } ], "prompt_number": 27 }, { "cell_type": "markdown", "metadata": {}, "source": [ "and Bob reads the resulting data:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars\")\n", "for row in bc:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 52642)\n", "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n" ] } ], "prompt_number": 28 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Can you explain what you observe in terms of what you have read about transactions? Are we in the middle of a transaction? \n", "
Alice's update has not yet been committed so has not become permanent in the database. Bob is reading the data that is permanent in the database.\n", "\n", "Two transactions are in progress, Alice's and Bob's.
\n", "\n", "What do you expect to observe if Alice now commits her update? In the next cell, write code to commit Alice's update then check what each will see when they read the data.
Alice's transaction has completed. Bob is still in the middle of a transaction. \n", "\n", "Code provided for now" ] }, { "cell_type": "code", "collapsed": false, "input": [ "aliceConnection.commit()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 29 }, { "cell_type": "code", "collapsed": false, "input": [ "ac.execute(\"SELECT * FROM cars\")\n", "for row in ac:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 56650)\n" ] } ], "prompt_number": 30 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars\")\n", "for row in bc:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(2, 'Mercedes', 57127)\n", "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(1, 'Audi', 56650)\n" ] } ], "prompt_number": 31 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The change has been made permanent in the database." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
Whichever database system we use, we need to be aware of the implicit rules of where transactions begin and end, and of the default behaviour.\n", "\n", "In psycopg2, which we are using here, the first command of a connection cursor automatically starts a transaction. If an error occurs, the transaction is aborted and no further commands are executed until a rollback() is executed. A transaction is ended by either a commit() or a rollback(). A commit() makes changes permanent in the database. If a connection is closed using close(), changes are not committed; instead there is a rollback.\n", "\n", "The psycopg2 module can also run in autocommit mode, in which case all changes are immediately made permanent. " ] }, { "cell_type": "heading", "level": 2, "metadata": {}, "source": [ "Locks\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A row which is updated becomes automatically exclusively locked until the end of the transaction. Other users cannot update the same row until the transaction has been either committed or rolled back. However they can update different rows.\n", "They can also read the data in the row." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Run the following and observe how two different rows can be updated by two different transactions. Also observe whether the SELECT reads the uncommitted updates:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "ac.execute(\"UPDATE cars SET price=56654 WHERE id=1\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 36 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars WHERE id=1\")\n", "row=bc.fetchone()\n", "print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 56650)\n" ] } ], "prompt_number": 37 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"UPDATE cars SET price=56670 WHERE id=2\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 38 }, { "cell_type": "code", "collapsed": false, "input": [ "aliceConnection.commit()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 40 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"UPDATE cars SET price=56690 WHERE id=1\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 41 }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars WHERE id=1\")\n", "row=bc.fetchone()\n", "print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(1, 'Audi', 56690)\n" ] } ], "prompt_number": 43 }, { "cell_type": "code", "collapsed": false, "input": [ "bobConnection.commit()" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 44 }, { "cell_type": "code", "collapsed": false, "input": [ "#aliceConnection.rollback()" ], "language": "python", "metadata": {}, "outputs": [] }, { "cell_type": "code", "collapsed": false, "input": [ "#bobConnection.rollback()" ], "language": "python", "metadata": {}, "outputs": [] }, { "cell_type": "code", "collapsed": false, "input": [ "bc.execute(\"SELECT * FROM cars\")\n", "for row in bc:\n", " print(row)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "(3, 'Skoda', 9000)\n", "(4, 'Volvo', 29000)\n", "(5, 'Bentley', 350000)\n", "(6, 'Citroen', 21000)\n", "(7, 'Hummer', 41400)\n", "(8, 'Volkswagen', 21600)\n", "(2, 'Mercedes', 56670)\n", "(1, 'Audi', 56690)\n" ] } ], "prompt_number": 45 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Include an exercise to experiment with isolation levels. Interleave two transactions running in READ COMMITTED isolation level.\n", "Should be as above - this is the default isolation level in POSTGRESQL - set isolation level to READ COMMITTED to be sure.\n", "\n", "A SELECT sees only committed data, except for previous UPDATES in its own transaction (they are not yet committed). Two identical SELECTS in the same transaction may return different data if another transaction commits between the first and second SELECT.\n", "\n", "\n", "BEGIN transactionb\n", "transaction b do UPDATE table\n", "BEGIN transactiona\n", "transactiona do SELECT from table (before transactionb committed)\n", "transactionb COMMIT\n", "transactiona do SELECT from table\n", "transactiona COMMIT\n", "\n", "Then run the same with transaction level for transactiona set to REPEATABLE READ as follows:\n", "BEGIN transactionb\n", "transaction b do UPDATE table\n", "BEGIN transactiona\n", "transactiona SET transaction ISOLATION LEVEL REPEATABLE READ\n", "transactiona do SELECT from table (before transactionb committed)\n", "transactionb COMMIT\n", "transactiona do SELECT from table\n", "transactiona COMMIT\n" ] }, { "cell_type": "heading", "level": 6, "metadata": {}, "source": [ "Possible exercise" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Provide two transactions as a sequence of commands in separate cells. First transaction executes in its entirety. Then second transaction executes in its entirety. Experiment with interleaving and see how much you can overlap the transactions by moving the cells up and down. Can you deduce some general rules for deciding what order will work and what will not?\n", "\n", "This may have to be in a separate notebook as it may involve halting the notebook each time a deadlock occurs... or can you find a way of resolving a deadlock?" ] } ], "metadata": {} } ] }