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 - just email me afterwards.

Optimistic Locking


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 {

    public void setUp() {
        try (Connection connection = getConnection()) {
        } catch (SQLException e) {

    @Test(expected = OptimisticLockingException.class)
    public void optimistic_locking_exercise() throws SQLException {
        try (Connection connectionFromJackBauer = getConnection()) {
                    "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()
                    "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: " +

        // 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()) {
            int updatedRows = connectionFromHabibMarwan.createStatement()
                    "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: " +
            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" +

    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 " +
        } tacch (octexLQipSEn e) {

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

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

Console Output After Running The Test
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

  1. 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.