• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC and Oracle : not working as I expected

 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to implement locking in Oracle using the SELECT FOR UPDATE statement.
When I test it over two sql*plus session, everything is as expected.


Next, I implemented a piece of code inside a method:

I then ran two simultaneous test cases to call this method. However, both ran without blocking. I expected one of the testcases to block at line 1.

Why is this so ? Any suggestions ? I am using Oracle9i.

Thanks a million

Pho
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pho,
Pardon me if I am stating the obvious, but did you remember to turn "auto-commit" off?

Good Luck,
Avi.
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avi,

Thanks for your question.

Yes autocommit=off. I purposely ran these log statements.



Incidentally if I were to lock the row in sql+plus; then run my java code, my java code hangs until I commit in sql+plus. It even works in reverse. i.e. I make my java code sleep for 10 seconds, and when I issue the same SELECT FOR UPDATE, my sql+plus session hangs until it is committed by the java code.

This is so frustrating. Aarrrgh!

Pho
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pho,
Is it possible that the two places you are running the JDBC from are in the same transaction? In that case, it wouldn't block because the transaction would already have the lock.
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne,

That's an excellent suggestion.

Let me describe how my java code is being called.


The JDBC connection is obtained from JNDI. I dumped "Connection.toString()" and they all were different output strings. Is this good enough to affirm that I have different Transaction context ?

Regards,

Pho
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pho,
You didn't say EJB was involved! (Well spotted, Jeanne!) EJB servers have their own transaction managers -- which are separate from the database transaction managers. I don't think your expectation is realistic -- because EJB is in the works.

Maybe you should describe the kind of behaviour you wish to obtain -- and then maybe we can tell you how to achieve it using SLSB's (or any other limitations/requirements you may still have not mentioned).

Good Luck,
Avi.
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avi,

Yes unfortunately I should've said that upfront.

Note:
  • My DAO is getting its JDBC Connection from a Local Managed DataSource in JBoss. So the DataSource should be using the transaction context from the SLSB.
  • My session bean method is set to use Container-managed transaction i.e. attribute = "Required".
  • I never call commit on the JDBC Connection. JBoss will do that once it returns from the SLSB method.

  • I don't think your expectation is realistic -- because EJB is in the works.

    Avi, I am interested to hear why this is not achievable. I just want that when multiple clients the SLSB Method, one of them will lock the row. And all other clients will block. Yes I could possibly synchronized on some shared monitor in the DAO; but I'd rather let the database do the work for me.

    Thanks

    Pho
     
    Jeanne Boyarsky
    author & internet detective
    Marshal
    Posts: 35279
    384
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Pho,
    It's not enough to have a different connection, you have to have a different instance of the SLSB. Otherwise, you leave open the possibility that the transaction is shared.

    You can test this out by using two separate Java test clients.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic