• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to update string to Oracle Clob

 
jammy chen
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20749
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic