Hi,
I have a gui to insert/update/delete on a Table.
When the user is editing a particular field, a lock to that row is obtained at the DataBase level.

When I go for EntityBean for this scenario,How do I acheive locking ?

Generally there are a couple of different scenarios based on the concurrency control algorithms of both the DB and the App server. These can be narrowed down to optimistic/pessimistic for this duscussion:
1. If the App server is pessimitic, it will usually implement what is known as "commit option A". In this case the server will only keep a single instance of the entity in memory, and block transactions that try to access the entity concurrently. So in this case, you don't really have to do anything.
2. If the DB server is pessimistic then for purpose of this discussion, if you use SERIALIZABLE isolation level, the DB will obtain a lock when you read the data and won't allow other instances to update it until you commit. So again, you have no work.
3. If both are "optimistic", which is a common case, you should obtain a row-level lock on the data yourself. A simple way to do that on most DBs is to add "FOR UPDATE" to the end of your SELECT statement. As a rule of thumb, I would use the SERIALIZABLE isolation level. It may not be necessary on some DBs, but I doubt the performance improvement will be worth the time it takes you to make sure of that.

If you want to be portable, you should code your entity bean to work in scenario 3, as described above.

Regards
Gal

As you said you are using GUI to Insert/Update/Delete. This approach will lead into long wait for other processes if you want to lock the row until you update it. I suggest for the GUI related application the better approach is not to lock the whole system until the user using the system finishes.

The scenario which can arrise in GUI application is user can open a UI screen and then walk-out for few minutes(10-20) and then come back and don't even change any field and close it. You had locked the rows so long just for display.

I would suggest you use versioning or time stamp for Update and delete(soft delete). Thus you don't hold the resource for long period. This will get you better performance as well as prevent concurrent updates.

I prefer this approach unless if you are doing some operation within an EJB method where the lock will be held for a brief time. In those approach you can have select lock and then to update.

Cheers
Venket

Mohan's approach is what you should use if you don't want to lock the table.
From your post, I udnerstood that you do want to lock it:
"When the user is editing a particular field, a lock to that row is obtained at the DataBase level.
When I go for EntityBean for this scenario,How do I acheive locking ? "

If you do want to achieve locking, you can't use optimistic concurrency control. Otherwise, I would strongly recommend using a version numbering approach.

Gal

If possible you should use optimistic locking.

If you really need a pessimistic lock, note that using 'select for update' means that you need to keep a transaction active from the time the user does the select to the time that they do the update (or they decide that they won't update at all). This means that you need to use a UserTransaction, and that each edit in progress will consume a DB connection.

Tom

Hi

One more thing to remember when leaving the locking to the database, and I think that you should do that (or perhaps the appservers EB's will do it but in the end there is a lock in the database to manage the transaction), is what kind of lock the database is applying to your row.
Is it a page level lock or a row level lock ?
If you have a small (~100 bytes) row on a machine with a page size of ie. 4Kb and use a page level lock there will be a lot of records (aprox. 400 ) locked and other users will have to wait for your lock to be released to access any of the other 399 rows. Figures are approximates :-)

Jan