|
You're
absolutely right saroj. I only was doing a summary of the versioning pattern.
The complete pattern I think Gene posted to theserverside.com as FatKey pattern
or something like that. But bottom line, it's a tradeoff. I've found that with
versioning, UNCOMMITTED works better for me, but it's a case by case thing(which
is the cool thing about setting TX_SERIALIZABLE, it's guaranteed to work, no
hassle on the coder). Basically, there's no easy way to achieve performance AND
data integrity period. The key is to find a balance between data corruption
odds and performance. Again I mention bank systems, they SERIALIZE every
operation they execute, using a Message Queue facade. I think I prefer a
facade over TX_SERIALIZABLE, but usually, I take my chances with
versioning, if I cannot find another way around it.
There's a great book called 'Principles of Transaction
Processing' that I found most interesting concerning these and other issues that
are quite common when implementing large systems.
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
Juan,
Yeah, This would happen if the TX is long. They should be as
small as possible but of course, some exceptions are bound to be
there.
READ_UNCOMMITTED (imho) is a problem when we have MASTER Tables and
Transaction tables refer to these MASTER Tables. If Master Records are
uncommitted
and
used for some updates and/or Insert and then the record is rolledback then DB
may have inconsistent state.
-Saroj
Hi
saroj, READ_COMMITED would work like this:
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 // Success!!!, Update count =
1
5. SELECT TEST_ID=n
If
User B hasn't committed the transaction when User A issues the update, The
second update succeeds as the change would not be reflected as User B's
transaction hasn't completed. It happens quite a lot when a single transaction
touches a lot of tables.
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
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.
|
