Announcement Announcement Module
Collapse
No announcement yet.
@Transactional default on non transactional methods Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Originally posted by al0 View Post
    And what is so exotic? It adheres to "serializable" definition (i.e. no dirty reads, no non-repeatable reads, no phantom reads). And even if you would search even this forum (and/or "Data access" forum) you will find samples of its usage. BTW, during TPC-C tests in which Oracle performs exceptionally well "seriazable" isolation level is a must.
    Well, it's the famous "ORA-08177: can't serialize access for this transaction" error that throws people off and makes the whole idea basically unusable for non-readonly stuff. Oracle themselves call it "optimistic serialization" and it goes against most people expect from that isolation level.

    Comment


    • #17
      Originally posted by dejanp View Post
      Well, it's the famous "ORA-08177: can't serialize access for this transaction" error that throws people off and makes the whole idea basically unusable for non-readonly stuff. Oracle themselves call it "optimistic serialization" and it goes against most people expect from that isolation level.
      As far as I understand, any multiversioning database would likely implement it in the similar way. Otherwise it would be concurrency killer. BTW, famous ORA-8177 in reality is not so terrible, as on many other databases you would have deadlock instead of it, which is not any better. And concerning peoples expectations - I shall to note that most peoples misinterpret the whole meaning of "serializable", as each can see from multiple discussions here and at other forums. The best source for info for "serializable" and Oracle is asktom.oracle.com, go there and make search e.g. "isolation serializable" or just "ORA-8177".

      Regards,
      Oleksandr

      Comment


      • #18
        The problem is that you wouldn't get deadlock or any other error using other databases, they would simply block and wait it out. Not highly concurrrent, but no one expects it to be concurrent, it's the highest isolation level there is.

        Anyway, I think this is the wrong place to discuss Oracle serialization details.

        Comment


        • #19
          As a matter of fact, I don't feel too much concerned with Oracle serialization details...
          This battle of wits reminds me a lot of those bet. Juergen vs Christian & Gavin on the same subject.

          Anyway, as I said, as in my case any communication with the database do occur within a transaction, I better demarcate them, that give me a better control on them.
          Though I'm sometimes wondering what would be the locking behavior of the DB if calling a lot of requests (that would normally open/close individual connection on the DB) within a demarcated RO Transaction.

          So the conclusion is that I haven't much possibilities to render my application more concurrent since my DB level is already read_commited and since removing RO demarcated transaction is not supposed to help.

          So what's left? Optimistic locking strategy vs. MVCC enabled RDBMS?
          What would be the most efficient?

          Aside of that, one of the best *simple* things I've read is to implement an aspect that would catch ConcurrencyFailureException and retry the operation a defined number of time.

          Also timeouts management (or non management by the HibernateTransactionManager) might also cause some locks on the DB as I heard. So going for a JTA transaction manager might also help.

          Any other advice :-)

          cheers guys.

          Comment


          • #20
            Originally posted by Jean View Post
            So the conclusion is that I haven't much possibilities to render my application more concurrent since my DB level is already read_commited and since removing RO demarcated transaction is not supposed to help.
            We don't know that for sure. What I said was a guess - how should it look like if no one touched the defaults. My suggestion would be to actually check it. MS should have some monitoring tools that allow you to see locks, waits, ongoing transactions, etc.

            Comment


            • #21
              Originally posted by Jean View Post
              As a matter of fact, I don't feel too much concerned with Oracle serialization details...
              This battle of wits reminds me a lot of those bet. Juergen vs Christian & Gavin on the same subject.

              Anyway, as I said, as in my case any communication with the database do occur within a transaction, I better demarcate them, that give me a better control on them.
              Though I'm sometimes wondering what would be the locking behavior of the DB if calling a lot of requests (that would normally open/close individual connection on the DB) within a demarcated RO Transaction.
              ...
              All request in the same (non-distributed) transaction are run in the same session/connection.

              Concerning your problem - more specific details are needed to give more specific advice. Do you have single bottleneck (i.e. some table or even record on which your locks stuck) or problem is dissipated (you have to wait on many different tables due to normal lock contention). If first, then slight change of algorithm may help. If second, that what you have said (MVCC DB or optimistic locking). Which kind of locks? Which operation are waiting, etc.

              Comment


              • #22
                dejanp, I've actually followed your recommendations and checked my system status (implemented a hook on my c3p0 to have info on the Connection). It was of great help. At least I know what's going on!
                It is default, as you said. That's why I came to these conclusions.

                Oleksandr, concerning our lock status, it is actually a problem to know what's really going on. We don't have access to the production system, we just receive the exceptions by email.
                From what the users says, from the email, it looks like it is due to normal lock contention.
                It's happening at different points (service methods) in the system, we get Spring's CannotAcquireLockException. But each time it's a SELECT request that have to scan an entire table (count rows, return a list with condition, etc.)
                It's actually kind of weird to me that this happens on SELECTs. If many users are modifying objects of the required table it's probably a normal behavior. From my reading, there's an automatic lock granularity escalation (roughly from row to page, from page to table), and with SQL Server 2000, the limit for escalation is supposed to be 5000 rows which is pretty weak figure compare to the amount of existing records in tables.

                Also, some locks seem to last forever. This is what led me to suspect a timeout problem.

                I don't really know how to monitor that from the Java layer, I don't think it's possible, probably just look at the exception, have a snapshot of the system and analyse.
                It'd probably be far easier to monitor the database.

                Regards,
                Jean

                Comment


                • #23
                  Well, yes - I don't think you can troubleshoot stuff like that without a good dba. Without pulling locking info snapshots at the right moment and analyzing them you are not going to get far I'm afraid.

                  CannotAcquireLockException on selects are most probably caused by simple deadlocks. Try to lookup which SqlException caused them.

                  Comment


                  • #24
                    Yes, one more vote for DBA assistance. BTW, as all exceptions occurs on SELECTs switching to multiversioning would solve it magically, as in multiversioning databases readers are never blocked. Have discuss migration to SQL Server 2005 that supports multiversioning?

                    As I have mentioned, I'm not expert on SQL Server, but most of databases that I have dealt with have some system views/tables that allow to see locked objects with some information who and how has locked them. I hope that SQL Server provides such functionality as well. In this case you may try to make select from such view to take snapshot of locks in the exception handler for CannotAcquireLockException.

                    Concerning timeouts - I'm not 100% sure, but I guess it is not so much functionality of transaction manager as of RDBMS itself. Try do discuss it with DBA.

                    Regards,
                    Oleksandr

                    Comment


                    • #25
                      thanks again for being so supportive!

                      Comment

                      Working...
                      X