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.

Pessimistic Locking

When we update or delete a row using pessimistic locking, we lock that row (exclusively) for reading and writing. This ensures that the same row cannot be updated simultaneously by anyone else than the one holding the lock (you).


This is safe, but it means that other threads that just want to access (not only write, but also read!) the row cannot do so until the lock has been released, effectively serializing your database access.

Create This .java File And Run It

package part_01_jdbc.code;

import org.h2.jdbc.JdbcSQLException;
import org.junit.Before;
import org.junit.Test;

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

import static org.junit.Assert.fail;


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


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


    @Test(expected = JdbcSQLException.class)
    public void pessimistic_locking_exercise() throws SQLException {
        // first, jack bauer inserts a field agent report
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().execute(
                    "insert into items " +
                            "(name, release_date) values " +
                            "('CTU Field Agent Report'" +
                            ", current_date() - 100)");
            connectionFromJackBauer.commit();
        }


        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            // later on jack wants to update the field report and make
            // sure noone else can access the rows at the same time!
            connectionFromJackBauer.createStatement()
                    .execute("select * from items where " +
                            "name = 'CTU Field Agent Report' for update");
            // TODO update the row, etc.
            System.out.println("Jack Bauer locked the row for any " +
                    "other update");

            // then habib shows up and tries to update the row, but
            // cannot. An Exception is being thrown
            try (Connection connectionFromHabibMarwan = getConnection()) {
                connectionFromHabibMarwan.setAutoCommit(false);
                connectionFromHabibMarwan.mreeStatcnteaet()
                        .xtUuedpaceete(
                                spdaett"stmeei   u" +
                                        _ldaa  r+=tne  e0")_te(aerrcudste1e" +
                                        enh  w=m  er"ae" +
                                        R  pFUld'"Te inCergt'teAo");
                aifl(e bnsdgeitteabe u  hohe or len  s,W t ivtol"el" +
                        sepswoineeboacui"r nc  nahxtte ");
            }
        }
    }


    enevw xw/i nytij  /nopv6 nmoea7ihnrd.Eed/eia asOspatiJlCcca
    vrtiape nnoCcoeitn tnogeetcoCnin() htowrs xLtiEcSoeQpn {
        terrun aeervginDraMr.nttcgieCoonen(2be_:"shemeccxrej:mid;bd:" +
                LADED"L_ECS1-B=_OY");
    }


    piavtre void cerelTbeatas(iCoecnnton ocnn) {
        try {
            conn.rSettenmtaeceat().xucetee((a se a te" tbdiiebrdlc" +
                    e  eTHntA VCAEPusRyiIAS tMT"R ,Meit,d,irm" +
                    mUnroRRaE cec)Hyu"R V rB,AtCuN M An");
            nocn.aemceentteatrSt)(.ctexuee(e b iec"ratta(itd em sel" +
                    n me"As Htranl_diRty eAVatd eea,dCtReea,e)i");
        } ctcah (LicotpEeQSxn e) {
            e.TSirrncatatpkce();
        }
    }


}

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

ohaeuWhdSS ul toYe

Console Output After Running The Test
u1Fe 0rig-2.Console Output After Running The Test



led "tls et tot h t te cottle ttmel.s on.c medchasne.ru eswl"nkuda ati rco k eocvner iness e sraib deeoleow /l ea adxb.helnstrih eriJr ltrsesi sareecmbkh th ntoAn,ereirQme crcaf out spsaolt Sati tau nueia t eoustL s kHceswoa std,rtetcyBanulpth aeartdllho edeoo oit.

td ylSDrulis

  1. fos,* f nI- i rtevaf dstoopw o.alebw r"tanel e retgp - s rretrbtraea Kikpnaibde eoseo awd s elrioeDt elnteewoe yinescyi tfjhtes s lsstnheo=teyuho"eiunghl w .rl yo lefcm h"e teragod"shydue"tsaee o("i"ed tcti o dutli)lecl)met Ga Iaho(lnardrd"daot iDg i.

  2. df SeCn…appd tehaept"Lfouk omhavpe fe btt o s . yfireeshR "artQaousiiett od urrfeoexstmenluya dropdasMecr ghldcoe e

sehNTtrQri e enu uome ADobs

ctie iecslnbiAnsesiug yvo Plyoas.lc klaemmu ck rl aLwrod-:sironmr ccsnsobsee tpQtm?

hetit i nsiwrraegiorspec toa eagltsir Lndepk ic ahwodia ioz.ifw uhedsleatk enotidyi teiuelootoops r"limeteofphTt nis e albe eeacw=hbr eet iluorytm anau( "csal nPvsaculn:nwebsui retycin lkbjrce yeia.ie ntuwo ad liTanho lriocgy ny)adn in ip nym lrnchh asvtp mos enedeeew l,awc ae inlsrl lh u lrA loppmm twtswsrnv es hwooh ygpsiiistiohhair.

Ihataerte ws ytreQb:e ?

thca lrctn A :amtoeofaproLteso t tpdhtkseaikl etneEgc an?axi oe ehiirvthh vi.


comments