Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update the table rows through cursor

 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I want to update the table rows by using cursors. Which one is the better way to use and why?
1)Select for Update
2)Where current Of
I am totally confused on this. Can you please help me out?

Thanks
Sudha
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT ... FOR UPDATE locks the rows specified in the WHERE clause of your SELECT. The lock is released when you COMMIT or ROLLBACK the current transaction. This can be useful e.g. if you want to make sure nobody tries to update the same rows at the same time. But it can also be risky, because it is easy to lock a lot of records unnecessarily e.g. by selecting all the rows even if you only want to update one, or lock other people out by doing the SELECT and then forgetting to commit/rollback the transaction. So you should be careful how you use this.

UPDATE ... WHERE CURRENT OF [cursor] does exactly what it says: it updates the current record in your cursor. This works when your cursor is doing SELECT ... FOR UPDATE i.e. you have locked the record ready for updating it, which should stop anybody else updating it between the time you read it and the time you update it.

But I'm no Java expert, so you should get advice on how to manage this transaction and read consistency stuff within your Java application.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic