Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 93566 invoked from network); 28 Dec 2005 19:48:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 28 Dec 2005 19:48:10 -0000 Received: (qmail 83789 invoked by uid 500); 28 Dec 2005 19:48:09 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 83182 invoked by uid 500); 28 Dec 2005 19:48:07 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 83167 invoked by uid 99); 28 Dec 2005 19:48:07 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Dec 2005 11:48:07 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.98.34] (HELO brmea-mail-3.sun.com) (192.18.98.34) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Dec 2005 11:48:05 -0800 Received: from fe-amer-03.sun.com ([192.18.108.177]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id jBSJlj3F024886 for ; Wed, 28 Dec 2005 12:47:45 -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 <0IS8009013S2RV00@mail-amer.sun.com> (original mail from Craig.Russell@Sun.COM) for derby-user@db.apache.org; Wed, 28 Dec 2005 12:47:45 -0700 (MST) Received: from [192.168.0.10] (c-24-6-172-77.hsd1.ca.comcast.net [24.6.172.77]) by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0IS80054B4BK8L10@mail-amer.sun.com> for derby-user@db.apache.org; Wed, 28 Dec 2005 12:47:45 -0700 (MST) Date: Wed, 28 Dec 2005 11:47:40 -0800 From: Craig L Russell Subject: Re: Select for update, cursors In-reply-to: <20051228161948.0708310FB004@asf.osuosl.org> Sender: Craig.Russell@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 X-Mailer: Apple Mail (2.746.2) Content-type: multipart/signed; protocol="application/pkcs7-signature"; boundary=Apple-Mail-160-725755734; micalg=sha1 References: <20051228161948.0708310FB004@asf.osuosl.org> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --Apple-Mail-160-725755734 Content-Type: multipart/alternative; boundary=Apple-Mail-159-725754715 --Apple-Mail-159-725754715 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi Danny, In my experience, your proposed implementation strategy generally won't scale past a few users. The reason is that you will have locks held during user think time, which is to be avoided for scalable applications. Alternative things to consider: 1. When the first user clicks on a transaction to edit, update the database immediately with the user id and date/time. This lets the second user know that someone else has reserved the right to update. This technique works well for infrequent updates (very commonly used for wikis etc.). You can embellish this technique with soft timeouts (user 1 can still commit changes even after the timeout unless another user has acquired the soft lock). 2. Allow as many users to access and update the transaction as you like, but only allow one to succeed. There are several techniques here, such as incrementing a number in the row on each update, and only allowing an update to occur if the user presents the correct update number. Most object-relational mappers use this optimistic locking strategy. The reason for updatable result sets is efficiency and ease of programming where there are a large number of things to update in the same result set. Instead of having to manage two different things (the original result set and the update set) you just manage one updatable result set. But IMHO it's not suitable for the scenario you are talking about with user think time involved. Craig On Dec 28, 2005, at 8:22 AM, Danny wrote: > We are moving from a local database application (1 user to 1 > database) to an > installation that will support a single database on a network (many > users on > 1 database). > > In looking into select for update, cursors, locking, etc. I have a > few > questions that maybe someone could help me with. > Let me preface this with the fact that I am in no way a database > programming > expert. > > Here is the scenario within the system: > 1. A user chooses a transaction from a list that they want to edit. > 2. User clicks edit and all the details of the transaction are > displayed in > the gui, the user has the option of editing any of these details. > 3. User clicks submit and the transaction is updated in the database. > > Here is where I have gotten so far: > 1. When the user clicks a transaction from the list and clicks > edit, that > transaction is retrieved from the database using a select for > update, which > returns a ResultSet with the following settings" > (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, > ResultSet.HOLD_CURSORS_OVER_COMMIT) > 2. Second user clicks the same transaction in the list and clicks > edit, that > transaction is retrieved using the same query. > 3. First user changes details in transaction and submits, database > update > occurs through the ResultSet produced from the select from update. > 4. Second user changes details in transaction, (not the new details > created > by user 1, but the original details) and submits, database update > occurs > through the ResultSet produced from the select from update.. > > The behavior that I want is: > - User 2 to generate some type of exception that I can catch: > - Either > At the time that they select the transaction in the list that User 1 > is already editing. > OR > At the time that User 2 submits the changes when User 1 performed an > update in the time since User 2 clicked edit to view the details of > the > transaction. > > It seems that what I want is a ResultSet.TYPE_SCROLL_SENSITIVE > which (from > what I can gather) derby does not support, although I am not sure > if this > would solve my problem. > > Anyone run into this sort of thing? Any suggestions? I have found some > commentary that the time honored tradition of comparing the last > update > timestamp column is really the best way to go about it, but then > why have > updatable ResultSets at all? > > Thanks > > > Danny Gallagher > The Gainer Group > 6525 The Corners Parkway > Suite 215 > Norcross Ga, 30092 > > > > > 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-159-725754715 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 Hi Danny,

In my experience, your = proposed implementation strategy generally won't scale past a few users. = The reason is that you will have locks held during user think time, = which is to be avoided for scalable applications.

Alternative things to = consider:

1. = When the first user clicks on a transaction to edit, update the database = immediately with the user id and date/time. This lets the second user = know that someone else has reserved the right to update. This technique = works well for infrequent updates (very commonly used for wikis etc.). = You can embellish this technique with soft timeouts (user 1 can still = commit changes even after the timeout unless another user has acquired = the soft lock).

2. Allow as many users to = access and update the transaction as you like, but only allow one to = succeed. There are several techniques here, such as incrementing a = number in the row on each update, and only allowing an update to occur = if the user presents the correct update number. Most object-relational = mappers use this optimistic locking strategy.

The reason for updatable = result sets is efficiency and ease of programming where there are a = large number of things to update in the same result set. Instead of = having to manage two different things (the original result set and the = update set) you just manage one updatable result set. But IMHO it's not = suitable for the scenario you are talking about with user think time = involved.

Craig

On Dec 28, 2005, at 8:22 AM, Danny wrote:

We are moving from a local database application (1 = user to 1 database) to an
installation = that will support a single database on a network (many users = on
1 database).

In looking = into select for update, cursors, locking, etc.=A0 I have a few
questions that maybe someone could help me = with.
Let me preface this with the = fact that I am in no way a database programming
expert.

Here is the scenario within the = system:
1. A user chooses a transaction = from a list that they want to edit.
2. User = clicks edit and all the details of the transaction are displayed = in
the gui, the user has the option of editing any = of these details.
3. User clicks submit and the = transaction is updated in the database.

Here is where = I have gotten so far:
1. When the user clicks a = transaction from the list and clicks edit, that
transaction is retrieved from the database using a = select for update, which
returns a = ResultSet with the following settings"
ResultSet.HOLD_CURSORS_OVER_COMMIT)
2. Second user clicks the same transaction in the = list and clicks edit, that
transaction = is retrieved using the same query.
3. First user = changes details in transaction and submits, database update
occurs through the ResultSet produced from the = select from update.
4. Second user changes = details in transaction, (not the new details created
by user 1, but the original details) and submits, = database update occurs
through the ResultSet = produced from the select from update..

The behavior = that I want is:
- User 2 to generate some type = of exception that I can catch:
- = Either
At the time that they select the = transaction in the list that User 1
is already = editing.
OR
At the = time that User 2 submits the changes when User 1 performed an
update in the time since User 2 clicked edit to view = the details of = the
transaction.

It seems = that what I want is a ResultSet.TYPE_SCROLL_SENSITIVE which = (from
what I can gather) derby does = not support, although I am not sure if this
would = solve my problem.

Anyone run into this sort of thing? Any suggestions? = I have found some
commentary that the time honored = tradition of comparing the last update
updatable ResultSets at = all?=A0



Danny = Gallagher
The Gainer Group
6525 The Corners Parkway
Suite 215
Norcross Ga, = 30092





=

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-159-725754715-- --Apple-Mail-160-725755734 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGHjCCAtcw ggJAoAMCAQICAw3FWTANBgkqhkiG9w0BAQQFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwHhcNMDUwMTEwMDA0MTA5WhcNMDYwMTEwMDA0MTA5WjBHMR8wHQYDVQQD ExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSQwIgYJKoZIhvcNAQkBFhVDcmFpZy5SdXNzZWxsQFN1 bi5DT00wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDti7ZE4rO6oXKbLM02AG9WY55t udmBVL53fb3V3X5S1kvcJOk1NEMIYT/T7Ww+/hE955zvHT29+mIoNe8AW/yj1WUH8uGG2HxhwCHI UQTHmN/ioVJgjwUaYbtNMKbL/NRpnL0QWewdMJS+6IFzFyX7ADFW5cJ+UWNLvNeWAQtN0mtLildn vdOgh50i8YPvACNkCHoomGjXx0azcXbe1X3c5AgRI6e2CZe5k2lRFQFUMqkjdoMtQPoNqJ1BxH9l i4cnabl8mcTwHHl44hrvb8ThqwRf2pfJh2vVuwmgK6z4IWjOk4RQM+0ODsRdq67mBdimJYmPMK1p RMBHzrUsfKxNAgMBAAGjMjAwMCAGA1UdEQQZMBeBFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTAMBgNV HRMBAf8EAjAAMA0GCSqGSIb3DQEBBAUAA4GBAIj86LzxCHedStDLMEeqHLy+UFG7zIRHfChSIV42 +MvXicydXEBh8v0Ry1V2d/lY4jS78G5yW5R9fKt1U5nlRBCOVzdhomvSolnNRIT71wPVVDrAIVlA YpXKxSmVBq7+4hV+3ZLHDeq3qZnNmiJR0sTEUD16xZX1RJs9dRYPCHoRMIIDPzCCAqigAwIBAgIB DTANBgkqhkiG9w0BAQUFADCB0TELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTES MBAGA1UEBxMJQ2FwZSBUb3duMRowGAYDVQQKExFUaGF3dGUgQ29uc3VsdGluZzEoMCYGA1UECxMf Q2VydGlmaWNhdGlvbiBTZXJ2aWNlcyBEaXZpc2lvbjEkMCIGA1UEAxMbVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIENBMSswKQYJKoZIhvcNAQkBFhxwZXJzb25hbC1mcmVlbWFpbEB0aGF3dGUuY29t MB4XDTAzMDcxNzAwMDAwMFoXDTEzMDcxNjIzNTk1OVowYjELMAkGA1UEBhMCWkExJTAjBgNVBAoT HFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBG cmVlbWFpbCBJc3N1aW5nIENBMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDEpjxVc1X7TrnK mVoeaMB1BHCd3+n/ox7svc31W/Iadr1/DDph8r9RzgHU5VAKMNcCY1osiRVwjt3J8CuFWqo/cVbL rzwLB+fxH5E2JCoTzyvV84J3PQO+K/67GD4Hv0CAAmTXp6a7n2XRxSpUhQ9IBH+nttE8YQRAHmQZ cmC3+wIDAQABo4GUMIGRMBIGA1UdEwEB/wQIMAYBAf8CAQAwQwYDVR0fBDwwOjA4oDagNIYyaHR0 cDovL2NybC50aGF3dGUuY29tL1RoYXd0ZVBlcnNvbmFsRnJlZW1haWxDQS5jcmwwCwYDVR0PBAQD AgEGMCkGA1UdEQQiMCCkHjAcMRowGAYDVQQDExFQcml2YXRlTGFiZWwyLTEzODANBgkqhkiG9w0B AQUFAAOBgQBIjNFQg+oLLswNo2asZw9/r6y+whehQ5aUnX9MIbj4Nh+qLZ82L8D0HFAgk3A8/a3h YWLD2ToZfoSxmRsAxRoLgnSeJVCUYsfbJ3FXJY3dqZw5jowgT2Vfldr394fWxghOrvbqNOUQGls1 TXfjViF4gtwhGTXeJLHTHUb/XV9lTzGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNV BAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25h bCBGcmVlbWFpbCBJc3N1aW5nIENBAgMNxVkwCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsG CSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDUxMjI4MTk0NzQxWjAjBgkqhkiG9w0BCQQxFgQU DZpUO+5egZcXmAxts3ror91imxIweAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNv bmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw3FWTB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UE BhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1Ro YXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMNxVkwDQYJKoZIhvcNAQEBBQAEggEA xSWcF87JaMUcLI/+mNvoThzZuLjMoL6A14LDb9THA7zTloPmQxCXooVPxLcumj+mT7bGCjGhwylm EDTWcAciThSQuiY2GI5IGX54gBTQJ03tWc8c7c8L86mcaKtbGydPpJnDJRkoL5KOoxYHjNKhX+7d vbjHKbv02nf6pRiEEJrkQ8DDKHwFQ4UXx5jKVwksAV4Reb2++ooRHUrXUqG47CLECczWp+0glKd3 XS5f/rTqeMQz9FWI3j1/4rWWmdYtqndK4DqrLAMjEn/o6ecVsE6DXvqP9+hfzNI8Q2/WZZ83vOt9 kuuyq5DcUBxJwlGNt0vCO/uOxb3c88WKHhz+LwAAAAAAAA== --Apple-Mail-160-725755734--