Hey,
I guess, if one sets
TX_SERIALIZABLE that would solve Sabastien's problem.
[JPL>] Yes, but there always are
tradeoffs.
The bottom line is the first
started Transaction always wins.
Say Bean1 and Bean2 are the two
bean instarnces pulled from the free pool which are being
executed....
say Bean1 corresponds to User1 and
Bean2 to User2
now, if Bean1 has first
strated the transaction then no matter what Bean2 has to wait till Bean1
commits the transaction
[JPL>] Precisely. Now Bean2 will wait N
seconds and, if it cannot execute, then the transaction will rollback. If
there were Bean3, Bean4, Bean5... BeanN(with the same pk) doing the same
thing, their transactions will rollback too. Some app servers rely on the
database to manage isolation, and some DBs (Sybase comes to mind) DO NOT
support TX_SERIALIZABLE. And some app servers let you specify the isolation
for the whole CMP bean. Add to it EJB 1.1 Commit Option C, no "isDirty"
optimization and a DB that only support page locks... and then you can only
execute 1 concurrent SQL statement to that table. Add to it different tables
doing serialized access and you have a system that only executes 1 transaction
at a time and the others fail miserably. So it's a tradeoff between max data
integrity and poor performance. If you're going TX_SERIALIZABLE all the
way(like most bank systems do), consider placing a Message Queue in front to
be able to process all transactions(like most bank systems do).
since the row is already locked in a transaction
( if Bean2 also wants to update the same record). One should note that Bean1
and Bean2 are not CMPs but stateless-session beans with embedded
JDBC.
[JPL>] Why? BMP beans also do just
fine.
if
Bean2 has first started the transaction then Bean1 has to wait till it commits
the transaction
So the
feature which Sabastein wants is "built-in" .
Even is one
implements Versioning-stuff, still the story remains the
same..
I guess one of the
most significant features of Vesioning is to detect dirty-value-objects
floating around in the system, detecting stale data in the system so that one
can get the datastructures refreshed with the fresh data. Vesioning is not to
stop/detect/start Concurrent record updates which forms the base of the
question asked by Sabastein.
[JPL>] Versioning achieves the desired
result. In fact, that is the way SELECT FOR UPDATE is implemented in
Oracle DBs (at least in 8i and above,
AFAIK).
while Bean1 is under
transaction if Bean2 tries another transaction it gets rolled back
!!!
Am I missing anything
here !
regards,
kris
Sebastien, if you search the list archives you'll
find tons of references into this subject. Here's a summary of the most
common solutions:
1)
Ask the container to serialize transactions (TX_SERIALIZABLE). It may depend
on the DB(some containers just delegate the isolation on the
DB).
2)
If using JDBC directly, and the DB supports it, lock records in the SELECT
statement. Oracle code:
SELECT FOR UPDATE * FROM TEST
Be
careful. All DBs that support this will aquire a write lock on the involved
records at best, but will fall back to a page and even the whole table if
they don't have enough resources to acquire and maintain a more
fined-grained lock. Only DB I know of that actually locks single records is
Oracle, which implements a field versioning algorithm. This is a better
performing solution than serializing access altough its not a standard RDBMS
feature and the gains in performance depend on the resources/load on the
RDBMS server(s).
3) Implement your own Versioning
Algorithm to solve races. Works like this:
-
Every table that supports versioning as an extra field named version(or
other, I used "vvvversion" in my implementations).
-
The isolation level must be READ_UNCOMMITTED(dirty
reads)
-
When you select a record you also get the version field.
-
You ensure that an update does not ocurr if the version field has been
touched and that the update operation changes the version field(to whatever
else, a sequence is nice).
1
- SELECT ID,NAME,VERSION FROM NAMES WHERE ID = 1
Result is (1, 'JPL', 23432)
2
- UPDATE NAMES SET NAME='Juan Pablo Lorandi', VERSION=19192 WHERE ID=1 AND
VERSION=23432
To
apply to your example:
User A
User B
1.
SELECT * FROM TEST
2.
SELECT * FROM TEST
3.
UPDATE TEST SET .... WHERE TEST_ID = n AND
VERSION=v //Success!!!, Update count = 1
4. UPDATE TEST SET ..... WHERE TEST_ID = n AND VERSION=v // Silent failure, Update count
= 0
The big difference between versioning and
serializing access is that the first client to issue the update wins the
race in versioning, whilst the first to execute the select wins in
serializing. Usually versioning performs better, whilst serialization
provides a more business logic friendly approach to isolation(maintaining
ACID rules).
HTH,
Juan Pablo
Lorandi
Chief Software
Architect
Code Foundry Ltd.
Barberstown, Straffan, Co. Kildare, Ireland.
Tel:
+353-1-6012050 Fax: +353-1-6012051
Mobile: +353-86-2157900
www.codefoundry.com
Disclaimer:
Opinions expressed are entirely
personal and bear no relevance to opinions held by my employer.
Code Foundry Ltd.'s opinion is that
I should get back to work.
Hi,
Please refer to page 135 (110 on book) of the
ejb design patterns book. It explains what attribute to use in a
situation ||ar to you.
--Ashwani
----- Original Message -----
Sent: Thursday, October 03, 2002
1:57 PM
Subject: Concurrent record
update
Hi,
I have some
questions about blocking concurrent update with multiple
clients.
I have
the following schema of transaction :
User A
User B
1. SELECT *
FROM TEST
2.
SELECT * FROM TEST
3.
UPDATE TEST SET .... WHERE TEST_ID = n
4. UPDATE
TEST SET ..... WHERE TEST_ID = n
In this
schema, two users are updating the same record but update from
B is lost ....
How can i do
that in my SessionBean, to throw an exception for user A when he
tries to
update the
record ?
I have read
some articles about database isolation level and transaction
level in EJB world
but i don't
see how to apply them to my case.
Thanks for
your help
Sebastien
Ce message est prot�g�
par les r�gles relatives au secret des correspondances ; il peut en
outre contenir des informations � caract�re confidentiel ou prot�g�es
par diff�rentes r�gles et notamment le secret des affaires ; il est
�tabli � destination exclusive de son destinataire. Toute divulgation,
utilisation, diffusion ou reproduction (totale ou partielle) de ce
message, ou des informations qu'il contient, doit �tre pr�alablement
autoris�e. Tout message �lectronique est susceptible d'alt�ration et son
int�grit� ne peut �tre assur�e. WFinance et WFinance Conseil d�clinent
toute responsabilit� au titre de ce message s'il a �t� modifi� ou
falsifi�. Si vous n'�tes pas destinataire de ce message, merci de le
d�truire imm�diatement et d'avertir l'exp�diteur de l'erreur de
distribution et de la destruction du message.
This message is
protected by the secrecy of correspondence rules ; furthermore it may
contain privileged or confidential information that is protected by law,
notably by the secrecy of business relations rule ; it is intended
solely for the attention of the addressee . Any disclosure, use,
dissemination or reproduction (either whole or partial) of this message
or the information contained herein is strictly prohibited without prior
consent. Any electronic message is susceptible to alteration and its
integrity can not be assured. WFinance and WFinance Conseil declines any
responsibility for this message in the event of alteration or
falsification.. If you are not the intended recipient, please destroy it
immediately and notify the sender of the wrong delivery and the mail
deletion.
