Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4

    Question Unanswered: Getting Deadlocks while using Command Center

    I have been using the Command Center to run some commands against my db and have noticed that while I am in the command center numerous deadlocks are occuring on the db, locking out production. From the command center I issue a command like below;

    select * from tphypkg
    - 1st 500 rows were displayed in the results window

    then i issued;
    update tphypkg set cstate = 'INVNTR', tslastchg = current timestamp
    -This update failed with a deadlock multiple times.

    I checked to make sure that nothing was running ... there were no programs running at the time on this machine. I began thinking that maybe I was locking myself out ... the data from the select was still displayed in the Command Center window. I closed the Command Center completely, then opened it again and did the same update command. This time it was successful.

    This implies to me that the Command Center does not do a commit after data has been selected and I may be causing the deadlocks in production when i issue selects. Does anyone have details about how and why the Command Center works this way. Meaning is the table locked as long as the data retrieved is still in the display window.

    Thanks for your help

  2. #2
    Join Date
    Feb 2002
    Posts
    96

    Re: Getting Deadlocks while using Command Center

    Yes. you are right. You are locking the table.
    Check the isolation level, which you have set in client config assist. It may be RR or RS.
    Change this to CS or UR, depends on your requirement.
    Hope this will help.

    Regards.


    Originally posted by smeier
    I have been using the Command Center to run some commands against my db and have noticed that while I am in the command center numerous deadlocks are occuring on the db, locking out production. From the command center I issue a command like below;

    select * from tphypkg
    - 1st 500 rows were displayed in the results window

    then i issued;
    update tphypkg set cstate = 'INVNTR', tslastchg = current timestamp
    -This update failed with a deadlock multiple times.

    I checked to make sure that nothing was running ... there were no programs running at the time on this machine. I began thinking that maybe I was locking myself out ... the data from the select was still displayed in the Command Center window. I closed the Command Center completely, then opened it again and did the same update command. This time it was successful.

    This implies to me that the Command Center does not do a commit after data has been selected and I may be causing the deadlocks in production when i issue selects. Does anyone have details about how and why the Command Center works this way. Meaning is the table locked as long as the data retrieved is still in the display window.

    Thanks for your help

  3. #3
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Getting Deadlocks while using Command Center

    Originally posted by cgprakash
    Yes. you are right. You are locking the table.
    Check the isolation level, which you have set in client config assist. It may be RR or RS.
    Change this to CS or UR, depends on your requirement.
    Hope this will help.

    Regards.
    Heys,

    Indeed the you´re the one causing the locks. Other alternative for your problem, besides changing the isolation in the Cliente Configuration Assistant would be:
    - Or you change it manually at the Command Center with the command:"CHANGE ISOLATION TO [CS;UR;RR;RS]", for your problem, you have to choose either CS or UR. That will prevent the locks to occour. But before issuing the command, you must disconnect from the Database, run the "Change Isolation" statement and then reconnect.
    But mind that the Isolation Level which you have set, is only active during the current connection. Once you disconnect and reconnect, the old Isolation Level will be reset.

    - Another option would be to insert a "Commit" between each statement. That will close the unit of work and prevent the locks.

    HTH
    Fernando.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •