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.
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
tYaSoh h uSduWeole

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