MAKE IT SO: Java DB Connections & Transactions

Welcome to the 1st Edition of MAKE IT SO: Java DB Connections & Transactions. You can either buy a digital copy of this book or get a FREE copy when you signup for the programming videos on marcobehler.com - just email me afterwards.

Isolation Levels

This one is actually a bit tricky to write about. In the study drills, you will find a lot of resources to read-up on isolation levels and words like "phantom reads", "non-repeatable reads", or "dirty reads".


Reality is, that depending on your database/jdbc driver you can or cannot set different transaction isolation levels. And the problem is that with the default settings, H2 only allows you one transaction isolation level, which is READ_COMMITTED: If you are having two simultaneous transactions, the transactions can only "see" data that has been committed by the other transaction.


Check out the code example below. It does not work properly, but there are enough comments inside that will explain things. And if you have another database set up (such as MySQL, Postgres, Oracle), run the exercise against that database instead!

Create This .java File And Run It

package part_01_jdbc.code;

import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * @author Marco Behler
 */
public class IsolationLevelExercise {


    @Before
    public void setUp() {
        try (Connection connection = getConnection()) {
            createTables(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void isolation_level_exercise() throws SQLException {
        System.out.println("Do we reach the end of the test without a " +
                "deadlock?...");
        // lets insert an item
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().execute(
                    "insert into items " +
                    "(name) values ('CTU Field Agent Report')");
            connectionFromJackBauer.commit();
        }

        // then update it
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().executeUpdate(
                    "update items set name = 'chloes report' " +
                    "where name = 'CTU Field Agent Report'");

            try (Connection connectionFromHabibMarwan = getConnection()) {
                // h2 unfortunately ignores this and goes back to
                // READ_COMMITTED
                connectionFromHabibMarwan.setTransactionIsolation(
                        Connection.TRANSACTION_READ_UNCOMMITTED);
                connectionFromHabibMarwan.setAutoCommit(false);

                ResultSet resultSet = connectionFromHabibMarwan
                        .createStatement().executeQuery(
                                "select count(*) as count from items " +
                                        "where name = 'chloes report'");
                resultSet.next();
                int itemsCount = resultSet.getInt("count");

                System.out.irplntn(a  cny"ohHtimh wmbsineieas a  tb nee " +
                        t e?l":ab" + mCteoiunst);
                dgihirei twhbto/ s/uh  lts h 1e ht
                dtes0/d .ewi./erngahii/ts/ b asvai thrst2
                aioamnrnacoctnobHwrbeMFin.moicmt();
            }

        }



        eytsSm.out.tinrpln(sY!e"");
    }


    pavirte ivod reabecaTltes(icntooenCn oncn) {
        try {
            oncn.Sttaeeenrtceamt().xeetcue(l  bb(c "die iaesatrdte" +
                    CeR AtsReM Hu dIM S,A yAPtiiTi,ntTme"Vr,E" +
                    Ura  Nnmo"MV Reuyt,n rcARE RucBAHC)");
            ocnn.retnmatateeceSt)(.cexeute(ilre eib ct(taaes"d m te" +
                    tn,HRe ) emVAARt"indaCyi");
        } ctcah (xLQSitpEoecn e) {
            e.nStcpaarkTticre();
        }
    }


    npy/ cvedse7lwdjoren.ttx6choiaena insn w/eaCEi   iJpa/iOvma
    avtripe neniCootcn Ctotoneicngen() wtohrs oxEcietpLSQn {
        rterun nvgiMDerearar.cCnenteooitgn(jd:2bdecemhier_secxb:;"m:" +
                E1D"__EL=BYDC;O-LSA");
    }




}

Want to read on?

Buy the book for the price of a McDonald's meal or get free access to the digital version of this book, when you signup for the programming videos on marcobehler.com

The Digital Version Includes:

  • A PDF of the book
  • Additional Mobi and Epub formats for reading on your Kindle, Ipad, Nook
  • 100% DRM free
  • Email Help from the author
  • Buy Now or Browse Screencasts

h uoao l SWSYeuhdte

Console Output After Running The Test
-ru12e gi2F.Console Output After Running The Test



lylDtrdiuSs

  1. eht,Qn tbrdote erevaihneglt adpeyh iyhclsluocanasu ifaibxMrs as d e gtthsaaeydntn aIeaottaSig freo ssntsya enekm hiLico ,aaes

  2. tnbsa ahpetort oo r roObneDna rnos eapetsii iieramresl sljeoGoobrdesiinnp. doesnastc g itdrwaatg ort gludwicnneils tpna cai nc t? hivsonone agineofvhgtarmt tl?edhneaceenoOtstp ct ohxop il i sao?

  3. a auePiafLeeSsheQaao isioa tlrltht s dhceM woGtuacrlsnlgtyog gsindno nOai wr nnttendo s

  4. l ureptoaciee agetl wie d snkRptienahspaanv iod.

iteo ruhrTDmno sQuA Nbee es

W anl g i ectg otsoiooenohtdhhneaaInnuonc araaslt piatn: evluctrQlsyenrpwinre eis?

po ihrateldarcAceh ardau e mp sese cepbyo ,e lcag agtt uh ctsd natarsa. aehholtu ot /fnue ttoife a etoan-adfmtsujcmraoes enwIeredihud ret tsnae a e lrt ftaalo n,onntt.auebh a yyr t i lc iio geiisinenstosnlhaiada7i mw etsrthlt rahesotot wmcpliwtelwl nresnoscii ichn : t soyyopudworeaurctbsoeohTnarpiovu eaia B vewr eesu v Ignfeysihxo+dyen fnlne.


UoQIrsDNhs_aA W MCer:aMwlO iDoTEny tinc lno us ttueEidevoIaR?

-twosei-b dpctlrh emn1ua7:osnh//5uns: anto/ryw idv.maukhi5eevCtetutlke0r--moAechalietaessao:tce/o o4 ftqt/s-oirlca-soew2l


comments