Hi
Juan,
I
would like to point out that READ_UNCOMMITtED is not mandatory to use VERSION
strategy. It works just as fine with READ_COMMITTED.
In
fact, READ_COMMITTED is better if app. has to ensure that only those records
are displayed which are committed.
-Saroj
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.
