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

How to update string to Oracle Clob

jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25
Mostly we used and still using this way:



As you can see it select firstly and then update, then I thought another way:

this way is simple and performance did a little better. but I am confusing basically very few people did like this, I wonder why?
so I want know is any difference with first way? or it might cannot work properly? any body can share your ideas?

Thanks your guys for taking a look and give suggestion, I actually want to know the second way can work properly?. or has it potential risk?


Java tutorial
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
In case 1, you acquire the lock on the row you are updating so that no one else can acquire the lock. So all other interested transactions who may try to update this row has to wait. So the advantage with approach 1, is that you dont have an issue of "lost updates".

I think this post needs to be moved to the JDBC forum. I am not sure how to move it. Next time around, please choose the forum carefully!
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

Sudheer Bhat wrote:In case 1, you acquire the lock on the row you are updating so that no one else can acquire the lock. So all other interested transactions who may try to update this row has to wait. So the advantage with approach 1, is that you dont have an issue of "lost updates".

If you use explicit transactions, by calling setAutoCommit(false) on the Connection before updating, then commit() after the update, you also get atomic behaviour.

I think this post needs to be moved to the JDBC forum. I am not sure how to move it. Next time around, please choose the forum carefully!

You're right about moving it to JDBC, but that's something that only Ranch staff can do. Fortunately, I'm a staff member. Off to JDBC we go!


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Rob Spoor wrote:
Sudheer Bhat wrote:In case 1, you acquire the lock on the row you are updating so that no one else can acquire the lock. So all other interested transactions who may try to update this row has to wait. So the advantage with approach 1, is that you dont have an issue of "lost updates".

If you use explicit transactions, by calling setAutoCommit(false) on the Connection before updating, then commit() after the update, you also get atomic behaviour.

The setAutoCommit(false) is necessary, but might not be sufficient. A little known fact is that when updating LOBs in Oracle, the containing row must be locked to prevent concurrent modifications (at least the 10g documentation states this, 11g seems to be a little bit different; I didn't dig deep in it though). Googling oracle lob update lock row yields some interesting read.

I think creating and inserting new LOB (in the spirit of update tbl set lob=empty_clob() where ... returning lob into ?) instead of overwriting the existing one would not need the row lock, since the newly created lob locator should not be visible outside current transaction. I'm not sure about it, though.
jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25
Martin Vajsar wrote:
Rob Spoor wrote:
Sudheer Bhat wrote:In case 1, you acquire the lock on the row you are updating so that no one else can acquire the lock. So all other interested transactions who may try to update this row has to wait. So the advantage with approach 1, is that you dont have an issue of "lost updates".

If you use explicit transactions, by calling setAutoCommit(false) on the Connection before updating, then commit() after the update, you also get atomic behaviour.

The setAutoCommit(false) is necessary, but might not be sufficient. A little known fact is that when updating LOBs in Oracle, the containing row must be locked to prevent concurrent modifications (at least the 10g documentation states this, 11g seems to be a little bit different; I didn't dig deep in it though). Googling oracle lob update lock row yields some interesting read.

I think creating and inserting new LOB (in the spirit of update tbl set lob=empty_clob() where ... returning lob into ?) instead of overwriting the existing one would not need the row lock, since the newly created lob locator should not be visible outside current transaction. I'm not sure about it, though.


so you means my second way is somehow incorrect updating method?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

jammy chen wrote:so you means my second way is somehow incorrect updating method?

I think it is OK. I did actually concentrate only on the first method, as it was part of the discussion.

You're in effect updating the lob locator in the row, and the new locator will not be visible outside your transaction until you commit. Moreover, and more importantly, the update does lock the row in the same way a select for update would. So the reason this method does not require an explicit lock is that it is locked implicitly anyway. I'm really sorry for the confusion I've caused.

The way I suggested (update tbl set lob=empty_clob() where ... returning lob into ?) comes somewhere from Oracle's documentation, and maybe (just maybe) it would perform even better. I think that your second method creates a temporary LOB which is then copied to the table's LOB segment when you execute the statement. The method I hinted creates the LOB directly in the table's LOB segment and writes data to it, so no more copying is involved. If your LOB is large, the copying might take some time. I've elaborated on this here. It is well possible that that code could be changed not to require casting to Oracle's classes, but it ties to Oracle anyway by means of the SQL used, so I didn't care.
jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25
I think it is OK. I did actually concentrate only on the first method, as it was part of the discussion.

You're in effect updating the lob locator in the row, and the new locator will not be visible outside your transaction until you commit. Moreover, and more importantly, the update does lock the row in the same way a select for update would. So the reason this method does not require an explicit lock is that it is locked implicitly anyway. I'm really sorry for the confusion I've caused.

The way I suggested (update tbl set lob=empty_clob() where ... returning lob into ?) comes somewhere from Oracle's documentation, and maybe (just maybe) it would perform even better. I think that your second method creates a temporary LOB which is then copied to the table's LOB segment when you execute the statement. The method I hinted creates the LOB directly in the table's LOB segment and writes data to it, so no more copying is involved. If your LOB is large, the copying might take some time. I've elaborated on this here. It is well possible that that code could be changed not to require casting to Oracle's classes, but it ties to Oracle anyway by means of the SQL used, so I didn't care.


Thanks for your answer, the second way is really I never used before, so I am very very careful about it so I afraid it occurs unexpected issue. I may worry to much. I guess it does not require an explicit lock because it is locked implicitly or internally anyway. but who can make sure it? You said ". A little known fact is that when updating LOBs in Oracle, the containing row must be locked to prevent concurrent modifications (at least the 10g documentation states this, 11g seems to be a little bit different;" can you post the link to the Oracle manual or any document? sorry about my so much questions.

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

jammy chen wrote: can you post the link to the Oracle manual or any document? sorry about my so much questions.


Oracle Database SQL Reference (10g) - documentation of the SELECT statement: http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_10002.htm - the page is terribly large, find the last occurrence of "for_update_clause" on it.

Oracle Database Application Developer's Guide - Large Objects (10g): http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_working.htm#i1010367 - see Locking a Row Containing a LOB.

I've found those googling for oracle lob update lock row 10g site:docs.oracle.com. If you're on a different version, try to find these documents from the version you're using.

I guess it does not require an explicit lock because it is locked implicitly or internally anyway. but who can make sure it?

Updating a row causes that row to be locked. There is absolutely no uncertainty about this. The explicit (FOR UPDATE) row locking is only necessary if the LOB locator has been read using simple SELECT statement, that does not lock the row unless the FOR UPDATE clause is used. If FOR UPDATE would not be used, several sessions (users) might read the same LOB locator and might try to modify it concurrently.
jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25
Martin Vajsar wrote:
Updating a row causes that row to be locked. There is absolutely no uncertainty about this. The explicit (FOR UPDATE) row locking is only necessary if the LOB locator has been read using simple SELECT statement, that does not lock the row unless the FOR UPDATE clause is used. If FOR UPDATE would not be used, several sessions (users) might read the same LOB locator and might try to modify it concurrently.


I really appreciated your helps. I have another question, what is difference between PreparedStatement.setCharacterStream and setAsciiStream? I want to update a java string, which includes English\Chinese\Japanese...Characters. Is it ok to Use setCharacterStream?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to update string to Oracle Clob