Re: Concurrent record update

[Juan Pablo Lorandi]

Title: Message

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. [EMAIL PROTECTED] 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. -----Original Message----- From: A mailing list for Enterprise JavaBeans development [mailto:[EMAIL PROTECTED]] On Behalf Of Ashwani Kalra Sent: Thursday, October 03, 2002 10:49 AM To: [EMAIL PROTECTED] Subject: Re: Concurrent record update 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 ----- From: BOUTTE Sebastien To: [EMAIL PROTECTED] 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.