• 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

row-level locking

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I am fairly new to SQL. We are trying to write a program that invokes a transaction that at one point reads data from a row and locks it temporarily so that another transaction cannot access it at the same time. Once the transaction has completed, the row is then updated/unlocked and available for another transaction to read/use. We are using the select for update sql statement but for some reason we still see two transactions waiting to update the same row, which is causing an error. I read that there are three types of locks, but I'm not sure how to use the correct lock. Any help would be greatly appreciated. BTW, we are using DB2.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use optimistic locking, which is keep an update timestamp or counter in the row and verify that it has not changed while performing an update.

Row-level locking isn't always enough because it doesn't prevent people from inserting new rows. For example, if you are looking for the most recent transaction or checking that a username is unique, row level locking will not help.

Oh, and for other types of locking such as table locking or pessamistic locking (the general form of row/table locking), most of those take big performance hits in modern systems unless in some cases updates are infequent.
[ December 14, 2005: Message edited by: Scott Selikoff ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic