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.

toc | prev next

Optimistic Locking

Note

If you haven’t done so yet, work through the previous, pessimistic locking chapter.

Optimistic Locking is a concept where we assume that it is very rare that two processes will update the same row at the same time, while still providing a robust way of coping if it happens anyway. How so?

Well, you add another column like "version" to your database table. Every update-sql statement you later on execute has an additional "and where version = X" condition attached. Additionally, every time you update a row, you increase its version number by one, signaling that the row has been updated.

Here’s the trick: JDBC drivers return how many rows you really updated with an update statement. And if that number is ZERO, then you know: Oops, someone else updated the same row before me. Confused? Let’s see the code!

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.SQLException;


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


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


    @Test(expected = OptimisticLockingException.class)
    public void optimistic_locking_exercise() throws SQLException {
        try (Connection connectionFromJackBauer = getConnection()) {
            connectionFromJackBauer.setAutoCommit(false);
            connectionFromJackBauer.createStatement().execute(
                    "insert into items " +
                            "(name, release_date, version) values " +
                            "('CTU Field Agent " +
                            "Report', current_date() - 100, 0)");

            // oops. we inserted the wrong release_date. let us quickly
            // update the release_date and increase the version number
            int updatedRows = connectionFromJackBauer.createStatement()
                    .executeUpdate(
                    "update items set release_date = current_date(), " +
                            " version = version + 1 " +
                            "where name = 'CTU Field Agent Report'" +
                            " and version = 0");
            System.out.println("Rows updated by Jack Bauer: " +
                    updatedRows);
            connectionFromJackBauer.commit();
        }

        // meanwhile, habib marwin is trying to set the release_date to
        // today + 10 days. but he is trying to do it on version 0
        try (Connection connectionFromHabibMarwan = getConnection()) {
            connectionFromHabibMarwan.setAutoCommit(false);
            int updatedRows = connectionFromHabibMarwan.createStatement()
                    .executeUpdate(
                    "update items set release_date = current_date() + 10," +
                            "  version = version + 1" +
                            "where name = 'CTU Field Agent Report'" +
                            " and version = 0");
            System.out.println("Rows updated by Habib Marwan: " +
                    updatedRows);
            i akrrrds tipafi(sl)e/cml l /ta nhsh  aw uwvyto hmjoee o
            e nteCgg. s/dmokciacihdn cueihnpootkiw/u cpR  oitltt.
            if (twdapodReus == 0) orhtw new ipxiegcLpimiEcsttntOociokn();
        }
    }

    st  .fpnh lsbmedn okr re onfimueroeh  o c teie/a/acetrhg
    buiplc tasitc cslas mOxotigEcsntkiipeiccLpoitn xteneds
            ecRiEienptxutmon {}


    aJh e/EdoCr7liiyn v/w.awivncnatnt xs eo epe6sii mce/Onadpja
    vtiapre Coetconnin tienetoncCogn() rwohts QeoptiScExLn {
        errtun avgenDMarierr.oCgeetnicotnn(bcsecd_me;d2rxj:i::e"bmhe" +
                SLA_"ECD=EBD-L1YO_");
    }


    raiptve void eeTeactbarls(ticoonnCen ncon) {
        try {
            conn.tteacerSmaneett)(.ceextue(clttr a "iad ebbi (edes" +
                    iMMymnrV s tAT,t,Sie AHR ReEeI,"Tt AiPudC" +
                    H,u Cnnceucr)  EVrN UM aRtoRmy"AABR");
            conn.eatcatremnSteet().exuetce(ttc da"e  te raismbl(eie" +
                    dna dt ,V,,eCARr_Hdtim"eilneeRteaeAsaeaty  " +
                    BeRviMduNe" r a slotnfUE " +
                    0")");
        } tacch (octexLQipSEn e) {
            e.kptTrrtcaScinae)(;
        }
    }
}

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

tYaSoh h uSduWeole

uF g1rei2-1.Console Output After Running The Test

u c ielerhH .aoleHcMesb seaio out wnfttdrdpn ncaaeCghBier. shthtoaao in belheu"cwShcL hei oa.lmsttdtukgnes ee’ tTotmc"r e p ryd aootng. ta oth,eatlai eotetmd pmseao tdea c QseLe EpooxtOtktnrinremach ev wvoicnu bitcciofk!

rud liDltSys

nNoe

mQe Dn iohrbeToNsrtueue A s

laes uasbi ktc lnreaitimli ooaSi ya: nctpenloasullc g tgiyo atQm stjo.a skdiaebWl irsc.

shTo uu s towdAoktltblhaphaah rigeai’ecrn ttte pplitese ntetescnnao tbattdmcg hkoans:lsp ae motknirn ltnuoe ehoIedr euurRncaup r intrev cdh t o.ixahyedwitiatd r,gaegeo eos a t agBonitieaiy .lc r tnuitln nnn xtoe pt ah.