Note to self... Don't forget to change profile to Derby before sending e-mail
to Derby list.
---------- Forwarded Message ----------
On Friday 06 January 2006 11:11 am, Danny wrote:
> What I was after was a way to build a list of transactions that are
> available to edit for a user.
OK, just to be clear, when you say transactions you mean data stored in the
row represents a transaction, and not a database transaction.
> What I don't want in the list is any transactions that are currently being
> edited by another user.
Ok.
So what you want is that if a result set is in use by another user, then you
can not see nor edit those rows?
Ok...
If I understand you, then here's a simple little trick that may work for
you... It sounds like a semaphore example problem...
Assumptions:
1) The tx_tbl is the transaction table that contains the data that your users
are working on.
2) There exists a single column like row_id that is unique to the rows in the
tx_tbl.
3) All of the users are using the same app to access the database and do not
have direct access to the data outside of the app.
Create a table lck_tbl that has at least the following columns:
user_id (the person who is currently working on the records)
row_id ( a unique identifier for the row being worked on)
Then all you need to do is the following within the app.
1) Lock the lck_tbl and the the tx_tbl
2) Perform your query on tx_tbl and lck_tbl to get your data... *
3) for each row in your result set, you insert a row in to lck_tbl
to indicate that it is in use.
4) You unlock the lck_tbl and the tx_tbl.
Now you can do whatever processing you want.
When done, you do the following:
1) Lock the lck_tbl and the tx_tbl
2) Apply the changes to the rows in the tx_tbl
3) Remove the rows from the lck_tbl
4) Unlock the lck_tbl and the tx_tbl.
* Note: You perform a query against the tx_tbl where the rows are not in the
lck_tbl.
I believe that this is a quick and dirty work around if you can't get the
databases isolation levels to work for you. (ie committed read, dirty read,
etc ...)
If you're using logging and transactions, then you'll have to do some
additional work.
HTH...
> I think that I am back to comparing the last update timestamp on the row,
> the first timestamp is pulled from the database when the
>
> user selects the transaction and then on the update of the transaction
> adding the last update that was pulled to the where clause, if I get an
> exception
>
> that that row doesn't exist, it is because it was updated by another user
> in the time since this user selected it for edit.
>
>
>
> Danny Gallagher
>
> The Gainer Group
>
> 6525 The Corners Parkway
>
> Suite 215
>
> Norcross Ga, 30092
>
>
>
>
>
> _____
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 05, 2006 6:59 PM
> To: Derby Discussion
> Subject: Re: Select records that are not locked?
>
>
>
> Hi Danny,
>
>
>
> On Jan 5, 2006, at 11:14 AM, Danny wrote:
>
>
>
>
>
> Is there a way on a select statement to select all rows that match the
> where
>
> clause criteria, except for any rows that are locked?
>
>
>
> I hope not. That's not the way I understand databases are supposed to work.
> The result of a query is not dependent on the state of the internal
> exclusion mechanisms.
>
>
>
> Perhaps what you want is "dirty read" (isolation level 0) where the select
> statement is not blocked by any locks...
>
>
>
> Craig
>
>
>
>
>
> 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:[EMAIL PROTECTED]
>
> P.S. A good JDO? O, Gasp!
-------------------------------------------------------
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
