Add a time stamp to database table,when display this record,pass the time stamp to web page,
when user submit the page,before update the record,we check whether the timestamp in database table is the same as the value store in web page,if not equals,means that this record has bean updated by others.

I will use a simple sample to descript this pattern.

This is a model J2EE frame.
The user use a browser to access our web applications.
we use servlet to display init page and accept user request,
and use Bean managed Entity Bean to maitain database table data.

Step 1: we build the application as usual.There is a bug that data will be lose when two users update the sample record simultaneously or user submit request twice.

Step 2: modify the database table add a time stamp field,to suite for all type of database,we use a number type field,init value is 1,every time the record updated,increase this field
Step 3: modify the entity bean,add this field,in ejbStore method,we increase this field first.when client program set the timestamp field,check the new value is same as the value store in ejb,if not equals then throw a Exception,tell client the data has bean changed by others.while ejbLoad,select and lock the record.The whole entity bean transaction property is "Require".
Step 4: when display the init page,use the entity bean to get data from database,store normal field and the time stamp field in the web page.The ejb will execute ejbLoad method,because the ejb transaction property is require,so after the transaction commit,the database lock is released.
Step 5: when user submit the page,get all data from request,and store them to the entity bean(include the time stamp),the entity eban will check whether the time stamp has bean modified.
In this section,the ejb will execute ejbLoad and ejbStore.If any one others update the record,the new timestamp will load into entity bean before client update.And the ejbLoad and ejbStore in a same transaction,the record will be locked util the transaction commit.

I have published an Optimistic Locking Pattern for EJBs in WebSphere Developer Technical Journal, June 2000.
See http://www7.software.ibm.com/vad.nsf/Data/Document2351?OpenDocument&p=1&BCT=3&Footer=1
In the meantime I have set up my own company. Any comments appreciated
Cheers, Martin (martin.weiss@mw-informatik.ch)

I think this is similar to make an semaphoro lock system.
Why does'n do sessions statefull ejb with bean managed transaction? the transacction can live among 2 different calls from the same user. In addittion put the transacction isolation as serializable. Then, nobody can write your row
if you have read it.

Sorry by my poor english level.

Fla,
you should try to have short commit cycles and a scalable solution. Your recommendation does not fulfil both criterias: stateful sessions do not scale, transactions (and associated locks) should not span user think-time.
Martin

Using CMP, different databases need different (SQL)statements while trying to acquire a read/write lock at the *database* level as opposed to optimistic/pessimistic concurrency or locking at the container/bean level.

For example, MS-SQL Server needs a "SELECT ... AT ISLOLATION SERIALIZABLE", Oracle needs a "SELECT ... FOR UPDATE" as a method of acquiring 'locks' or in other words at a transaction isolation level 'Serializable' to prevent dirty/unrepeatable/phantom reads.

So it is difficult (to say the least) to use "generic" sql clauses in conjunction with transactions and locks at the database level without resorting to vendor specific clauses.

And hence the need for a simple Timestamp/versioning mechanism in EJB 1.1 (even EJB 2.0 pfd 2 seems to imply that the acquiring a read/write lock at the db level is upto the ejb vendor - which ejb vendors might implement - or not at their discretion). All that the timestamp/versioning does is compare versions when the client sends data over for modification. The reading could have been done by different clients in different transactions. So if another client tries to update the same data in the entity bean instance, the version numbers will not match if the data has been updated by another client - and an exception can be raised that effectively tells the client to 'refresh' the information i.e. get the data again to see what might have changed since the client first requested it (for modification). This is analogous to performing a "update <table> set <fields> where <fields = fields_read_at_transaction_start> and the only difference is the above technique works across transactions i.e. it prevents a client from overwriting [committed] changes made by another client.

The same issue is discussed in a slightly more detail - at EJB Design Patterns in the paper by the same name. (pages 21-23). Though the paper was written for the Borland AppServer, the strategies mentioned in the paper should be applicable for pretty much any complaint vendor.

-krish

Martin:
1. Session as entity facade.

2. Write a timestamp has the same efect as
have a transaction beetween 2 calls
(If your database uses a timestamp/cache transaction
system. i.e. Oracle has a deadlock system detector,
in addition u can put a transaction timeout)

3. If u want your client to be able to rollback the
changes, u need to do this.

Time to time have noticed that basic practises are called patterns
get real!!!

Genri,

With all due respect - what is a basic practice to you might not be the same for others. Considering the fact that someone has taken the time to post what he/she feels is something worth discussing is commendable.

And besides, it is not very heartening to see a reply along the lines of 'Get Real !!'. Keep in mind that real world projects based around the EJB hype are only just taking off - and of course you would expect to see some kind of discussion on best practices - maybe even a trivial (to you) discussion or two.

-krish

My replys are for the pattern, no for a person.


I have been studing this pattern hardly because I
need to do something similar in my final project of
university studies.

I have read the article of borland and of the person ¿name? from ibm. This method is slightly different to the first one, but it has similar effects and problems.


I think this pattern is a way to keep a transaction between
2 client calls.
A transaction with another isolation level (an special way of nested transaction), in fact with a curious isolation level because it tryes to solve the problem of lost update,
((in my country "actualizacion perdida" I don't know how
it can be well transalated.)) But it doesn't solve another problems that can appear.
For example:
I read data from row A,
Another user writes A.
(1)I do many others operations.
I re-read data from row A but ops! the data has changed.
Are operations in (1) are wrong?(non repetible read)


I think, this way to maintain a
transaction beetwen 2 diferent calls is a simple copy
from the way databases manage transactions.
(There are 2 ways, locks o timestamping, of course I'm
talking about the second one)

To do this "nested transaction" serializable you will need
read_timestamp and writetimestamp, etc etc. (see transaction
managers algorithms) but you cant rollback the nesteds transactions because it isn't a real nested transaction.
Then, a lock system is more appropriate.


But, I think:
1. Ejb spec must be increased to let nested transactions.
(the ejb 2.0 spec tells it is not done because nested transactions are very difficult to perform in a database
system.

2. You must use client transaction demarcation instead of
trying to do nested transactions. If isolation level between
two calls is, for example , RC you dont need yours entity
uses (RR or S) Using RC with a database like oracle
(with a timestamping technique) no lockings will be
made, except for write,write. With a querytimeout:
!you have solved the same problem that is related
in this pattern!

3. The ejb spect tells than isolation level problem is delegated to the database manager. If you need an optimistic transaction manager algorithm, you should use a database that supports it (oracle, sysbase,
sqlserver..., hundreds of them)

About the article from ibm.
If your database use a optimistic tecnique, you dont
need to reinvent this. You can use it.
If it isn't suppoted, you can get a book that that explain
techniques to perform this one, which is the thing you are trying to do.


Comments will be pleased.

Fla,

There was quite a nice discussion on the EJB-INTEREST list a while ago and maybe it gives you more information.

Real-world limitations of simplistic optimistic concurrency

On the whole:
  - having client demarcated transactions are a bad idea
    (why move such logic to the client side & besides if
     you do so - the transaction manager will have to
     co-ordinate calls between processes which will be
     horribly inefficient)
  - nested transactions are a no go.
    (rdbms support is extremely limited to say the least
     and besides the ejb spec (1.1 & 2.0 pfd2) explicitly
     states that for the moment, j2ee compliant vendors
     should not use nested transactions & besides nested
     transactions are not going to help solve concurrency)
  - locks should not span user think time.
    (as Martin Weiss points out in his posting)

btw, the article you mentioned 'of borland' written by yours truly - just a little clarification - I am not an employee of Borland, I just happen to use their products (as well as other EJB Server vendors).

-krish

Having a timestamp/version on a table doesn't solve the problem entirely. It looks to me that there must be one timestamp for an entity bean (need to take care of composite relationships). For example, if an OrderItems are owned by the Order and if an OrderItem is changed, the timestamp on the Order must be changed.

Any comments?!!

Anyway, I used a version number instead of timestamp. Do you guys see any disadvantages?

Thanks
Krishna party

Each database table has a timestamp (or version number) field. In your one-to-many sample the Order# is foreign key in the OrderItem table. In my pattern setting the foreign key is pushed to the db (before insert and before update triggers), i.e. the EJB provider has not to worry about the timestamp setting.

Krishna Party,

There are some subtle non-trivial issues here. A lot of it depends on your EJB vendor implementation. You mention (for example) that if a dependent/referenced object changes then the timestamp of parent/owner must be changed as well. Not necessarily. Why should it be changed?

But assuming that referenced/related/dependent (or whatever the case might be) object needs to be changed - and it is the responsibility of some enterprise bean's logic to take care of it, then the EJB vendor should ensure that two transactions do not commit at the same time i.e. only one of them should succeed. So user timestamping is a moot point here.

The above is admittedly a grey area - as (1) different ejb vendors implement this in different ways - some by serializing access to entity beans, others by some form of pessimistic/optimistic locking at the container level and (2) at the database level - rdbms's use different syntax's for transactions & locking

The link I sent in the previous post (Optimistic Concurrency) has information on the same topic.

A version number is in many ways better than a Timestamp. I found to my chagrin that a sql Timestamp (if used as I described in my post & paper) could be rounded off to the nearest second depending on your database and/or jdbc driver and hence you might hit a concurrency without being aware of it - if for example multiple users *request and update* the same data all within the same second.

-krish

Hi community,

I would like to ask the following. So far as I understood from this discission and from reading a helpful reference to the EJB-INTERSET thread there are no difference between short and long transactions (both can violate business data intergity) if the following conditions are true:
1. one uses CMP ejb with a version/timestamp field.
2. ejb container operates in B or C options (multiply ejb instances per the same PK)
3. DB isolation level is read commited (because optimistic concurrency).

Am I somewhere wrong or it means that this pattern is safely applicable only to BMP ones ?

Thank you for clarifications

Alex

Why are different users updating the same rows in the database? Do you have a real-world need for this? Personally I have never seen a need for record locking. If you have multiple users updating the same records, there is a problem with...

1. your business process
or
2. your database design

There is always a possibility of users updating the same record !! You do not seem to have enough experience to comment on this or any other technical matters....

Jason,

the optimistic concurrency problem haas been discussed for as long as I have been involved in OLTP systems ( way back in the 1970's).

The problem with application algorithm solutions is just that... they are application algorithms and don't necessarily apply to any other application or database update function.
This is a major weakness which I observe in J2EE/EJB discussions. They seem to assume that the J2EE/EJB will be the only function using the underlying data(base). Now this may be true in many cases but cannot be assumed in many others. A hidden danger is that it may be true for you today but may not be true when other applications are added in the future.
(Oh of course they should re-use your components and not do their own, but does everyone look both ways before they cross the street? and anyway one day J2EE may be as old-hat as a CICS/COBOL application.)

Basically a timestamp is not enough.
To be safe you would have to retain and check the old value of every column which contributed to the update values you are about to write regardless of whether they came from the same table as the one being updated or were themselves read-only.
Obviously this is very purist and in reality you may be able to determine acceptable business risk or devise delta-based checking "UPDATE ...Set column = :newvalue WHERE column + :delta = :newvalue ". But if you want to practice defensive design/programming then you would have to include the extra protection.
 
All the best, moore

Hi,
I'd like to continue the discussion about the timestamp, when an Entity Bean
has other's table references inside.
Point 1)
I think it's not necessary to check multiple timestamp in this case.
Suppose I have Table A which has among its fields a reference (id_client)
to as foreign key from Table B.

Table A Table B
------- -------
Name id_client
Address Description
id_client

Now the matter is what happens if I modify the Table B, altering id_client,
that is referenced in Table A?
On my opinion it's not necessary to check 2 timestamps in this situations,
because the DB won't let me change id_client if there are child records in TableA.
Please correct me if I'm wrong but this is Oracle behaviour. It's not possible
to specify that when id_client changes, then all its child records are updates.
This is called "cascade updates" and I had to write a Trigger to have this achieved
on a Table

Point 2)

>This is analogous to performing a
>"update <table> set <fields> where <fields = fields_read_at_transaction_start>
>and the only difference is the above technique works across transactions i.e.
>it prevents a client from overwriting [committed] changes made by another client.

Please kris could you expand a bit more, with a minimalist scenario ? I'd like
to compare this scenario with the timestamp solution.

Regards
Francesco

..sorry the table "structure" didn't come very clean on
the post. Here is it>

Table A
-------
Name
Address
id_client


Table B
---------
id_client
Description


Bye
Francesco

Thank you Marcioni for supplying an excellent reason why all of this fuss over timestamps is worthless. Updating a primary key? And in turn updating all foreign keys that reference this key?

This is one of the dumbest things I have ever heard.

But thank you, because you have proven my previous argument that the need for record locking in a database is the result of bad database design and/or a poorly designed business process.

My solution to this is:
a) Use a dirty_data numeric field - don't use dates, they get rounded on some DB's and you can have trouble with XML streaming and localisation - eg british summer time.
b) Only interact with entity beans via value objects.
c) Use bean managed persistance and validate the dirty_data field within the ejbStore function.

This pattern is built into my BMP, EJB code generator LowRoad.

So, when a web page accesses the data the sequence is
1. user requests page
2. jsp/servlet finds the EJB, and uses getValues
3. jsp/servlet uses values object to format the page for presentation, including the dirty_data value
4. User gets the html page and edits it, then submits
5. jsp.servlet finds the EJB again, and builds a local value object, which it submits to the EJB in setValues
6. The ejbStore validates the dirty_data field and throws an exception if the value is different, if not then it gets incremented and ejbStore continues.

This should work unless the container vendor gets in the way and doesn't call ejbLoad/ejbStore before and after the setValues - could happen if you set its config wrong.

Jonathan

p.s. Jeff, to make a flame work you have to be more subtle.

We have used the timestamp pattern in a different scenarion where EJB had not been used. We created two fields in the database

timestamp date
sessionid varchar

We allowed users to lock a record when timestamp=null and sessionid=null.

Once the record is locked, no other client can request it for update. The timestamp is sent back to the client like the session id.

When an update request comes to the database, check to see if the sessionid is same as the session id of the locked record. If not update disallowed.

When a client requests a locked record for update, check to see if the timestamp in the database is + the timeout period is < current time. If yes, then release the previous lock and lock the record for the new client. Later if the previous client sends an update, it will not be processed as described above.

This simulated the 2-tier database locking mechanism in a very effective way and scalability never became a problem. Similar pattern can be adopted in the EJB world as well.

Rajib

Hi

I've been looking at the Optimistic Locking Pattern using various methods such as Timestamping, Versioning and even state Comparison.

Some of the solutions are very ingenious, but I have a few concerns to these solutions.

They (the solutions) seem to only solve the problem where you objects are mapped directly to one table / one row in a database (Client object = 1 row in the ClientTable). You have an object instance with a version number or timestamp and you compare it to a record in a db (either using UPDATE ... WHERE "timestamp" or checking the version number of your object with the one 'originally created'.)

How do you use these methods when you have a heavy weight object comprised of relations and/or information from multiple related tables?

I.E.

I have a "Client" that 'has-an' "Address" and a "InvoiceList". The "Address" in the database could be a combination of some text fields and even foreign keys to a country / state / Municipality table. The InvoiceList could be many many rows from the InvoiceTable.

Even using Lazy instantiation with my objects to avoid instantiation unless needed. I stll can have a whole heck of alot of logic required to check all thse timestamps upon update.

Any pointers / suggestions?

Hi,

Well I think this is truely the best solution to cope with web applications. What I would suggest to you is to have a look at the JDBC Specs. You will notice that they implement already this feature with the cache-rowset and the web-rowset.

Chears

JeanLuc

If the front-end is a web application, then the user is entitled to use the "back" button and submit the form twice, with different data. I emphasize the word "entitled".

In your application logic, don't display a message: "another user has modified this record while you were viewing it". Incorporate some more logic to find out who did the last update, and decide whether to accept the change as is, ask for confirmation, or refuse the submission.

Using only a timestamp as gate keeper is prone to get you some phone calls from angry users.