Return-Path: Delivered-To: apmail-db-jdo-dev-archive@www.apache.org Received: (qmail 89508 invoked from network); 14 Mar 2006 19:30:52 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 14 Mar 2006 19:30:52 -0000 Received: (qmail 29575 invoked by uid 500); 14 Mar 2006 19:30:52 -0000 Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: jdo-dev@db.apache.org Delivered-To: mailing list jdo-dev@db.apache.org Received: (qmail 29564 invoked by uid 99); 14 Mar 2006 19:30:51 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Mar 2006 11:30:51 -0800 X-ASF-Spam-Status: No, hits=0.4 required=10.0 tests=HTML_FONT_BIG,HTML_MESSAGE,MISSING_HEADERS,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.98.36] (HELO brmea-mail-4.sun.com) (192.18.98.36) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Mar 2006 11:30:50 -0800 Received: from fe-amer-02.sun.com ([192.18.108.176]) by brmea-mail-4.sun.com (8.12.10/8.12.9) with ESMTP id k2EJUUuf021732 for ; Tue, 14 Mar 2006 12:30:30 -0700 (MST) Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0IW400G01U0Q5Q00@mail-amer.sun.com> (original mail from Craig.Russell@Sun.COM) for jdo-dev@db.apache.org; Tue, 14 Mar 2006 12:30:29 -0700 (MST) Received: from [129.145.133.119] by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0IW400HQ7U6SSC40@mail-amer.sun.com> for jdo-dev@db.apache.org; Tue, 14 Mar 2006 12:30:29 -0700 (MST) Date: Tue, 14 Mar 2006 11:30:26 -0800 From: Craig L Russell Subject: Re: Optimistic locking - not 100% reliable without triggers? In-reply-to: <44170E94.8030603@artnology.com> Sender: Craig.Russell@Sun.COM Cc: jdo-dev@db.apache.org, JDO Expert Group Message-id: <4E4FCEED-8CB5-45CE-91CD-56FCA533AE90@Sun.COM> MIME-version: 1.0 X-Mailer: Apple Mail (2.746.2) Content-type: multipart/signed; protocol="application/pkcs7-signature"; boundary=Apple-Mail-155-848670018; micalg=sha1 References: <001101c6475f$1e3828e0$0f64a8c0@libelis.com> <0869ADCD-37CC-4C03-A066-664A68D7A755@Sun.COM> <44170E94.8030603@artnology.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --Apple-Mail-155-848670018 Content-Type: multipart/alternative; boundary=Apple-Mail-154-848669282 --Apple-Mail-154-848669282 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Hi J=F6rg, On Mar 14, 2006, at 10:42 AM, J=F6rg von Frantzius wrote: > Hello Craig, > > do you or does anybody else see a problem in deferring verification =20= > and update of versions to the end of the transaction? In my experience, deferring verification and update of versions to =20 the end of the transaction is best practice. Of course, if you use =20 the flush method the implementation has no choice. Craig > Compared to JPOX' current implementation, in my opinion this would =20 > decrease database deadlock probability a lot. At the moment, =20 > locking would be dispersed over time of the transaction, as =20 > verification happens with every update request issued. For =20 > implications on the number of statements, please see the said JPOX =20 > issue. > > Regards, > J=F6rg > > Craig L Russell schrieb: >> Oops, >> >> On Mar 14, 2006, at 9:53 AM, Craig L Russell wrote: >> >>> Hi Eric, >>> >>> On Mar 14, 2006, at 4:01 AM, Eric Samson wrote: >>> >>>> Hello J=F6rg >>>> >>>> >>>> >>>> One common portable solution to this is to acquire locks during =20 >>>> the conflict detection step (using SELECT for UPDATE instead of =20 >>>> simple SELECT). >>>> >>> This would typically involve a user configuration setting, either =20= >>> globally or on a per-class basis. It's been implemented in e.g. =20 >>> the SunOne application server CMP as policy "lock-when-loaded" on =20= >>> a persistent class. >> >> This is noise. Doesn't apply to optimistic scenarios. Eric's =20 >> comments are spot on. >> >> Craig >>>> >>>> Another approach could be to perform the conflict detection and =20 >>>> the update at the same time (statement like =93UPDATE WHERE pk=3DOID = =20 >>>> AND TS=3Dts=94), but it raises some concerns for the conflict =20 >>>> resolution (most JDBC drivers are not able to indicate which =20 >>>> rows raised an exception). >>>> >>> I don't follow this. The UPDATE statement only updates one row at =20= >>> a time so I don't know why this would be an issue. This is the =20 >>> recommended solution from me. >>> >>> Craig >>>> >>>> >>>> >>>> Best Regards >>>> >>>> ....: Eric Samson, Founder & CTO, xcalia >>>> >>>> Service your Data! >>>> >>>> De : J=F6rg von Frantzius = [mailto:joerg.von.frantzius@artnology.com] >>>> Envoy=E9 : mardi 14 mars 2006 12:20 >>>> =C0 : jdo-dev@db.apache.org >>>> Cc : JDO Expert Group >>>> Objet : Optimistic locking - not 100% reliable without triggers? >>>> >>>> >>>> >>>> Hi, >>>> >>>> JPOX optimistic version verification entirely takes place within =20= >>>> the VM, by reading a version column and comparing it with an =20 >>>> expected value. When it verfies OK, JPOX proceeds and updates =20 >>>> the version column with a new value. That means verification and =20= >>>> update of version do not happen atomically, at least not on the =20 >>>> database level, unless at least REPEATABLE_READ is used. >>>> >>>> Now if two threads or processes want to update the same row, and =20= >>>> happen to verify the row's version at the same time, it is =20 >>>> theoretically possible that they both decide to update it, i.e. =20 >>>> none of them will receive a JDOOptimisticVerificationException. =20 >>>> Using READ_UNCOMMITTED instead of READ_COMMITTED for verifying =20 >>>> the version column will increase chances of detecting a =20 >>>> conflict, but still a conflict can remain undetected. >>>> >>>> In Oracle's suggestion for implementing optimistic locking, the =20 >>>> process will write the same optimistic version that it had =20 >>>> previously read, and a trigger on the database will do the =20 >>>> verification and increment the version if it had not been so =20 >>>> yet. I guess that the trigger executes atomically, so conflicts =20 >>>> will always be detected. >>>> >>>> Am I wrong here somewhere or do we really need triggers to have =20 >>>> 100% reliability of conflict detection? >>>> >>>> Thanks for any hints, >>>> J=F6rg >>>> >>>> >>> >>> Craig Russell >>> Architect, Sun Java Enterprise System http://java.sun.com/=20 >>> products/jdo >>> 408 276-5638 mailto:Craig.Russell@sun.com >>> P.S. A good JDO? O, Gasp! >>> >> >> Craig Russell >> Architect, Sun Java Enterprise System http://java.sun.com/products/=20= >> jdo >> 408 276-5638 mailto:Craig.Russell@sun.com >> P.S. A good JDO? O, Gasp! >> > Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:Craig.Russell@sun.com P.S. A good JDO? O, Gasp! --Apple-Mail-154-848669282 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252 Hi=A0J=F6rg,

On = Mar 14, 2006, at 10:42 AM, J=F6rg von Frantzius wrote:

Hello = Craig,

do you or does anybody else see a problem in deferring = verification and update of versions to the end of the transaction? =

In my = experience, deferring verification and update of versions to the end of = the transaction is best practice. Of course, if you use the flush method = the implementation has no choice.

Craig

Compared to JPOX' current implementation, in my opinion = this would decrease database deadlock probability a lot. At the moment, = locking would be dispersed over time of the transaction, as verification = happens with every update request issued. For implications on the number = of statements, please see the said JPOX = issue.

Regards,
J=F6rg

Craig L Russell = schrieb:
Oops,

On Mar 14, 2006, at 9:53 AM, = Craig L Russell wrote:

=
Hi Eric,

On Mar = 14, 2006, at 4:01 AM, Eric Samson wrote:

Hello = J=F6rg

=A0

One common portable solution to = this is to acquire locks during the conflict detection step (using = SELECT for UPDATE instead of simple SELECT).

=
This would typically involve a user = configuration setting, either globally or on a per-class basis. It's = been implemented in e.g. the SunOne application server CMP as policy = "lock-when-loaded" on a persistent class.
=

This = is noise. Doesn't apply to optimistic scenarios. Eric's comments are = spot on.

=
Craig
=

Another approach could be to = perform the conflict detection and the update at the same time = (statement like =93UPDATE WHERE pk=3DOID AND TS=3Dts=94), but it raises = some concerns for the conflict resolution (most JDBC drivers are not = able to indicate which rows raised an = exception).

=
I don't follow this. The UPDATE statement only = updates one row at a time so I don't know why this would be an issue. = This is the recommended solution from me.

Craig
=

=A0

Best Regards

....: Eric = Samson, Founder & CTO, xcalia

Service your Data!

De=A0: J=F6rg von = Frantzius [mailto:joerg.von.frantzi= us@artnology.com]
Envoy=E9=A0: mardi 14 mars 2006 12:20
=C0=A0: jdo-dev@db.apache.org
Cc=A0: = JDO Expert Group
Objet=A0: Optimistic locking = - not 100% reliable without triggers?

=A0

Hi,

JPOX optimistic = version verification entirely takes place within the VM, by reading a = version column and comparing it with an expected value. When it verfies = OK, JPOX proceeds and updates the version column with a new value. That = means verification and update of version do not happen atomically, at = least not on the database level, unless at least REPEATABLE_READ is = used.

Now if two threads or processes want = to update the same row, and happen to verify the row's version at the = same time, it is theoretically possible that they both decide to update = it, i.e. none of them will receive a JDOOptimisticVerificationException. = Using READ_UNCOMMITTED instead of READ_COMMITTED for verifying the = version column will increase chances of detecting a conflict, but still = a conflict can remain undetected.

In Oracle's = suggestion for implementing optimistic locking, the process will write = the same optimistic version that it had previously read, and a trigger = on the database will do the verification and increment the version if it = had not been so yet. I guess that the trigger executes atomically, so = conflicts will always be detected.

Am I wrong here = somewhere or do we really need triggers to have 100% reliability of = conflict detection?

Thanks for any = hints,
J=F6rg

=
=

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo=
P.S. A good JDO? O, = Gasp!

=
Craig = Russell
Architect, Sun Java = Enterprise System http://java.sun.com/products/jdo=
P.S. A good JDO? O, = Gasp!


=

Craig Russell

Architect, Sun Java Enterprise System http://java.sun.com/products/jdo=

408 = 276-5638 mailto:Craig.Russell@sun.com

P.S. A = good JDO? O, Gasp!


= --Apple-Mail-154-848669282-- --Apple-Mail-155-848670018 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw ggJyoAMCAQICEB7Y7fLHmKoeHTUdbtYrvpAwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIxNTIxNDc0NFoXDTA2MTIxNTIxNDc0 NFowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMXXgEAm88nu8cFbxXSWqTq+GaYpCx/0QSom 8kBbVxpJIURuO4ErLDupeXu6y9+5e7ZKLbeSQE5xbfYPcQR/IhqmYKy5YqRcuzPXREBj2zKEkZph pNXWpHgMdx9W8dq0Cu2i9Ux/S6c2MuEHrP6gfGGll+b/mzLoO280QHTuE4pcpKntRnwZdGxQ/5l8 IL+eLP+jpJAbYW9C+KNKofZtS6V6R0uzlqTOsEdZvwxZQ4mmPgHoz1+Gjwme/PC5sKvF09MaJDiI pj9SvZ4CTCgcDZV78J086YwlVbMC0VQotjhu1p42lr8CS33IXLz3OWNrDETCAepah/Dgw2ZZApQ9 9L0CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC MAAwDQYJKoZIhvcNAQEEBQADgYEAKdIkgAWCg2Bi7ocnstfJA4iymTRI2/L4oQx9zvllM9bNJ2cR cecJIx3HuoHbhPvemh1GExEPgHU+dXSxDmD0BEmPnhSReKCURyslnbMphPZ5kR6USzQFrRa+v0ii J+SBO9VQYTQWT+xEjmRLM76MfkBFw3IOC9CUkRoYZ88pOoUwggM/MIICqKADAgECAgENMA0GCSqG SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+ hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC 3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECEB7Y7fLHmKoeHTUdbtYrvpAwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwMzE0MTkzMDI3WjAjBgkqhkiG9w0B CQQxFgQUlq6AftQw3rxP7bv67dBss2p85CswgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe2O3yx5iqHh01HW7WK76QMIGHBgsqhkiG 9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe 2O3yx5iqHh01HW7WK76QMA0GCSqGSIb3DQEBAQUABIIBABuC0a3HMBMChDVnIECVQYx+AqTq51fW CN/g2ggfB4BYo6Ve1mLEraW44aU1JuK4/A2j388/syoiL6e1Q8MZlWJOoV0ITHqEsD1rRbKap6Go Vjw+Ag/JjV5J0xhZNFZviOUDHrfBQRHSKCy4CBwW+NkydrTKSxDeTig3a1CNbrNaQ6OUIc7BSWHC QldHnCBupPxEhMfBwMpuVG/qo65BgTe52Is0xZIU25MwxBzCiwetW6z9viaqkHWocHjRMrg8SA3N Xtc+EfBppSEVLnFdS9wJBV/KiAS3OgoqdaM1cRhtOELTcdqFzI5nXCRAp4Trw6SxY2q7Wi7vSuue i8GvV08AAAAAAAA= --Apple-Mail-155-848670018--