Databasteknik: A transaction example from Mimer

Step Transaction 1 Transaction 2
1
Log in with BSQL
Log in with BSQL
2
select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30

                  3 rows found

select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30

                  3 rows found

3
SQL>insert into worker values (4, 'Tom', 1000, 20);
SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20

                  4 rows found

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20

                  4 rows found

4
SQL>start transaction;
SQL>insert into worker values (5, 'Zeb', 1000, 20); 
SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20

                  5 rows found

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20

                  4 rows found

5
SQL>commit;

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20

                  5 rows found

6
SQL>start transaction;                             

SQL>start transaction;

7
SQL>insert into worker values (6, 'Jeb', 1000, 20);

SQL>insert into worker values (7, 'Dib', 1000, 20);

8
SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          6 Jeb          1000          20

                  6 rows found


SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          7 Dib          1000          20

                  6 rows found

9
SQL>rollback;
SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20

                  5 rows found

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          7 Dib          1000          20

                  6 rows found

10
SQL>start transaction;      
SQL>insert into worker values (6, 'Jeb', 1000, 20);
SQL>insert into worker values (7, 'Dob', 1000, 20); 
SQL>select * from worker;    
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          6 Jeb          1000          20
          7 Dob          1000          20

                  7 rows found

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          7 Dib          1000          20

                  6 rows found

11
SQL>commit;

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          7 Dib          1000          20

                  6 rows found


12

SQL>commit;
MIMER/DB warning -10001 in function EXECUTE
         Transaction aborted due to conflict with other transaction

13

SQL>select * from worker;
        WNO WNAME      SALARY    LOCATION
=========== ===== =========== ===========
          1 Bob          1133          10
          2 Liz          2200          10
          3 Sam          1100          30
          4 Tom          1000          20
          5 Zeb          1000          20
          6 Jeb          1000          20
          7 Dob          1000          20

                  7 rows found

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 31 oktober 2007