• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Confused about Locking

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Too many questions .
There is a very good book available in the market.

Java Transaction Processing
 
osman cinar eren
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
even one answer for any of them is welcomed. These are topics discussed in forums.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Can you shoot lasers out of your eyes? Don't look at this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic