• (cs) in reply to lofwyr

    lofwyr:

    I have a question (only got Sybase/Oracle/a bit DB2 experience): You really can't delete two different records in the same table from two different sessions without causing a lock?

    l.

    I hope you mean deadlock instead of lock, because otherwise your question is idiotic.

    And yes, you can delete two different record in the same table from two different sessions without causing deadlock.

  • (cs) in reply to JohnO
    JohnO:

    lofwyr:

    I have a question (only got Sybase/Oracle/a bit DB2 experience): You really can't delete two different records in the same table from two different sessions without causing a lock?

    l.

    I hope you mean deadlock instead of lock, because otherwise your question is idiotic.

    And yes, you can delete two different record in the same table from two different sessions without causing deadlock.



    The truth is in the middle ;-)  Of course every delete causes a lock, but it doesn mean one session (the first one) causes the other one to wait (imagine table-level-locking to get the picture). That would not be a deadlock, but definitely undesirable.
  • (cs) in reply to ammoQ
    ammoQ:
    JohnO:

    lofwyr:

    I have a question (only got Sybase/Oracle/a bit DB2 experience): You really can't delete two different records in the same table from two different sessions without causing a lock?

    l.

    I hope you mean deadlock instead of lock, because otherwise your question is idiotic.

    And yes, you can delete two different record in the same table from two different sessions without causing deadlock.



    The truth is in the middle ;-)  Of course every delete causes a lock, but it doesn mean one session (the first one) causes the other one to wait (imagine table-level-locking to get the picture). That would not be a deadlock, but definitely undesirable.


    Don't confuse the locking you get with *sessions* versus those you get using *transactions*. 

    If you are not sure how things work, try it!

    1. open up query analyzer.  Connect to Northwind.  execute the follwing SQL:

    begin transaction
    update customers set Region=Null where CustomerID='ALFKI'

    Note the following:

    a) we have not committed the transaction
    b) this update is only affecting 1 row in the table
    c) the Region is already null for that customer so don't worry about screwing up your Northwind DB

    2. Now, open up another copy of Query Analyzer.  connect to Northwind.  execute the following SQL

    select count(*) from Customers

    Let me know what happens.  

    Then, go back and think about that question you asked earlier about multiple users manipulating the data in a table using transactions and how that affects row counts returned from the tables.

    I'd be curious to hear how Oracle handles the same situation.
  • (cs) in reply to Jeff S
    Jeff S:


    If you are not sure how things work, try it!

    1. open up query analyzer.  Connect to Northwind.  execute the follwing SQL:

    begin transaction
    update customers set Region=Null where CustomerID='ALFKI'

    Note the following:

    a) we have not committed the transaction
    b) this update is only affecting 1 row in the table
    c) the Region is already null for that customer so don't worry about screwing up your Northwind DB

    2. Now, open up another copy of Query Analyzer.  connect to Northwind.  execute the following SQL

    select count(*) from Customers

    Let me know what happens.  

    Then, go back and think about that question you asked earlier about multiple users manipulating the data in a table using transactions and how that affects row counts returned from the tables.


    Unfortunately, I don't have an SQL Server ready for trying, but consindering what I believe to know about SQL Server I guess the select count(*) blocks until the first session finishes the transaction.


    I'd be curious to hear how Oracle handles the same situation.


    Oracle writes the update into the table, but keeps the old version in the rollback segment (aka "undo"). When the second session does the count(*), it automagically sees the old version of the data in the rollback segment. Thus neighter blocks nor sees uncommited data.
    Once the first session does a commit, a "switch" is flipped and the uncommited write becomes  visible for others, while the rollback segment is cleared. For that reason, doing a rollback is more expensive than doing a commit in Oracle.
    BTW, I guess you haven't browsed through the whole thread, otherwise you might have noticed
    sdfszgs ' posting about "snapshot isolation" in SQL Server 2005, which is the same thing. The only difference is that Oracle does it by default (you cannot even prevent it), while you have to ask SQL Server explicitely for it.
  • sdfszgs (unregistered) in reply to ammoQ

    Yup.  Which is why Oracle was/is the choice "de jour" for high transaction systems (among other reasons).

  • (cs) in reply to ammoQ

    ammoQ:
    Oracle writes the update into the table, but keeps the old version in the rollback segment (aka "undo"). When the second session does the count(*), it automagically sees the old version of the data in the rollback segment. Thus neighter blocks nor sees uncommited data.
    Once the first session does a commit, a "switch" is flipped and the uncommited write becomes  visible for others, while the rollback segment is cleared. For that reason, doing a rollback is more expensive than doing a commit in Oracle.
    BTW, I guess you haven't browsed through the whole thread, otherwise you might have noticed
    sdfszgs ' posting about "snapshot isolation" in SQL Server 2005, which is the same thing. The only difference is that Oracle does it by default (you cannot even prevent it), while you have to ask SQL Server explicitely for it.

    Unless Oracle fails to implement the various isoloation levels defined in SQL 92, your one explanation of it's behavior above assumes a certain isolation level.  Which one is it?

  • sdfszgs (unregistered) in reply to JohnO
    It's pretty obvious that it's Read Commited.
  • (cs) in reply to JohnO
    JohnO:

    Unless Oracle fails to implement the various isoloation levels defined in SQL 92, your one explanation of it's behavior above assumes a certain isolation level.  Which one is it?



    Default is "Read commited"; it is possible to set the isolation level to "serializeable".
  • frzx (unregistered) in reply to twks
    twks:
    <string><string><string>
    </string></string></string><string><string><string>Wow, this is a very impressive WTF!  In fact this code should have its own thread.  My guess is that the foreign key did actually exist as a separate column in the </string></string></string>ITINERARYITEMS table, but this developer thought this would be more of a challenge.


    I finally got off my butt and submitted it, so maybe it will get a thread someday. Actually, there is no column you can get that data from. There is a lot of useful data that is only in the XML.
    You can imagine how fun writing reports is.

    Another favorite of mine in this system is that we have:

    table Vendor, with a foreign key to table VendorTypes
    table Vendors, with a foreign key to table VendorType

  • Firewireguy (unregistered) in reply to Runtime Error
    Anonymous:
    I wonder what would happen if they removed a user?

    They could have just logically closed off a user kind of like:

    UPDATE users SET closing_status = 'closed' WHERE user_id = 3112

    SELECT * FROM users WHERE user_id = 3112 and closing_status = 'not_closed'

    Therefore never actually deleting any records.

    If they were using it to get the next user_id then surely it would be best to use

    SELECT count(1) FROM users

    thus not having to return all the records
  • (cs) in reply to Firewireguy
    Anonymous:


    SELECT count(1) FROM users

    thus not having to return all the records


    In which database systems is count(*) slower than count(1)?
  • zamie (unregistered) in reply to Jeff S

    Well to be honest at least in Oracle

    3) Select count(1) from table

    would be the most efficient one.

  • (cs) in reply to zamie
    Anonymous:

    Well to be honest at least in Oracle

    3) Select count(1) from table

    would be the most efficient one.



    That's an urban legend. Maybe it that was true for earlier versions but no longer.
    Let's try it (this database is currently idle, no other users tamper the results...):


    SQL> select count(1) from all_tab_columns;

      COUNT(1)
    ----------
         34765

    Abgelaufen: 00:00:02.22
    Let's repeat it, because we know caching will make it faster next time...


    SQL> /

      COUNT(1)
    ----------
         34765

    Abgelaufen: 00:00:00.90
    SQL> /

      COUNT(1)
    ----------
         34765

    Abgelaufen: 00:00:00.75
    SQL> /

      COUNT(1)
    ----------
         34765

    Abgelaufen: 00:00:00.75

    Now let's compare that to count(*)


    SQL> select count(*) from all_tab_columns;

      COUNT(*)
    ----------
         34765

    Abgelaufen: 00:00:00.73
    SQL> /

      COUNT(*)
    ----------
         34765

    Abgelaufen: 00:00:00.73
    SQL>


  • (cs) in reply to ammoQ
    ammoQ:
    Anonymous:


    SELECT count(1) FROM users

    thus not having to return all the records


    In which database systems is count(*) slower than count(1)?

    I'm not trying to say it is.  I use count(1) personally.  But that wasn't the point of my post.

Leave a comment on “A Representative Sample”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article