aspose file tools*
The moose likes JDBC and the fly likes Confused about Locking Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Confused about Locking" Watch "Confused about Locking" New topic
Author

Confused about Locking

osman cinar eren
Ranch Hand

Joined: Jan 25, 2005
Posts: 78
Hi,

I would like to learn about the locking issue in databases.
Firstly two concepts:
1) Pessimistic Locking through FOR UPDATE usage in SELECT statements: We can use this in our transactions, start the transaction, do the select-> you have the record locking with the FOR UPDATE, so nobody can select this record, do your operations on this record, commit-> Lock has finished. Am I right?

2) Optimistic Locking: I dont know how to enable this, but what happens is as follows: In an UPDATE statement on a row(record), if another update is issued before than yours, your UPDATE returns a result of 0 which means the record isnot updated. So if we use this in a transaction like;
select and show the result
update the record
commit
the select may have shown an incorrect value because after the select someone can UPDATE our record before our UPDATE runs. Am I correct? Also how do we enable Optimistic Locking?

Also there are the locking issues in the SCJD certification which is independent from the Databases, implemented by the user? Read Write Locks, Can you write on them?

Also there are locking issues through the Transaction Isolation Levels? Do they work for all of the DBs we reach with JDBC? Can we trust them? If we can use them, do we need the others?

Thanks in advance.
Best regards...


SCJP/SCWCD
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Osman,
As far as I know, each DBMS handles locking issues differently, and each DBMS's documentation usually provides excellent descriptions of how the particular DBMS handles locking (and isolation) issues. So I suggest you turn to your DBMS's documentation.

Good Luck,
Avi.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Too many questions .
There is a very good book available in the market.

Java Transaction Processing
osman cinar eren
Ranch Hand

Joined: Jan 25, 2005
Posts: 78
even one answer for any of them is welcomed. These are topics discussed in forums.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by osman cinar eren:
1) Pessimistic Locking through FOR UPDATE usage in SELECT statements: We can use this in our transactions, start the transaction, do the select-> you have the record locking with the FOR UPDATE, so nobody can select this record, do your operations on this record, commit-> Lock has finished. Am I right?


No. Other thread can select that row but can not acquire a lock on it. So, it means other thread cannot also update/delete that row but can select it ,as i said earlier.

Moreover, the thread, who executes SELECT . . .FOR UPDATE query, will get row level lock, if available. The row locks are released when the transaction that contains the SELECT...FOR UPDATE is committed or rolled-back.

[ May 03, 2005: Message edited by: Adeel Ansari ]
[ May 03, 2005: Message edited by: Adeel Ansari ]
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by osman cinar eren:
2) Optimistic Locking: I dont know how to enable this, but what happens is as follows: In an UPDATE statement on a row(record), if another update is issued before than yours, your UPDATE returns a result of 0 which means the record isnot updated. So if we use this in a transaction like;
select and show the result
update the record
commit
the select may have shown an incorrect value because after the select someone can UPDATE our record before our UPDATE runs. Am I correct? Also how do we enable Optimistic Locking?


It doesn't happen always.

Just assume you fired an update on a record. You placed a particular condition on primary key column and change some other column/columns. Some other thread has fired an update on the same record prior to your update and modified the record but not the primary key column. Then your query would still be valid, because that update didn't changed your conditioned column.

For this we used to have an attribute in our VOs known as version. Just to make sure that the record is not stale.

where
VO = Value Object Pattern - VO/TO Pattern
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Here are some gifts for you.

locking.pdf - ( its about ORACLE specifically.)
JDBC Isolation Levels
[ May 03, 2005: Message edited by: Adeel Ansari ]
osman cinar eren
Ranch Hand

Joined: Jan 25, 2005
Posts: 78
Hi Adel,

thanks for your great help. Optimistic/pessimistic locking is now OK for me.

in your case, do you change the version by youf or the DB does it for you?

also would you like to write some on locking in SCJD ?
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Not sure. But I think it is about the same optimistic/pessimistic locking.
For a better answer you can use SCJD Forum at Javaranch.

Thanks.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by osman cinar eren:
in your case, do you change the version by youf or the DB does it for you?


Yes, DB doesn't change it automatically on every update, we have to handle it.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Confused about Locking