Database transactions and concurrency - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 457

Transaction A-Send Orders Transaction B-Cancel Order

SELECT o.VERSION, …
FROM PLACED_ORDER o
WHERE ORDER_ID = x
(reads version = V1)

SELECT o.VERSION, …
FROM PLACED_ORDER o
WHERE ORDER_ID = x
(reads version = V1)

UPDATE PLACED_ORDER
SET STATUS='SENT'
VERSION = VERSION + 1,
WHERE ORDER_ID = x
AND VERSION = V1

COMMIT

UPDATE PLACED_ORDER
SET STATUS='CANCELLED'
VERSION = VERSION + 1,
WHERE ORDER_ID = x
AND VERSION = V1
(fails because VERSION = V1 + 1)

Figure 12.1
An example of how optimistic
locking handles concurrent updates

ROLLBACK

Time

changed row and redo just that part of the computation. In either case, it would
discover that the order had been sent and could not be canceled.

Benefits and drawbacks
Optimistic locking has a couple of advantages:

It is easy to implement in a JDBC/ i BATIS application, and it is supported by
many persistence frameworks.

■

Optimistic locking, unlike pessimistic locking, does not prevent an applica-
tion from using certain SQL SELECT statement features. As you'll see a bit
later, some databases have restrictions that prevent pessimistic locking from
working with some kinds of views and nested SELECT statements, etc.

■

There are, however, various drawbacks and issues:

All potentially conflicting transactions must use optimistic locking. Other-
wise, errors will occur. Fortunately, this isn't an issue when using a persis-
tence framework because optimistic locking is specified declaratively on a
per-class basis, which ensures that it will be used consistently.

■