Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Sybase deadlock question

    I have a stored procedure that sporadically causes a deadlock error, and is killed by the Sybase server, thereby allowing the other process to continue.

    Is there any way to discover what the "other process" was?
    (it may be an SQL statement run via ISQL, or another stored procedure, or Java code ... etc.)

    Also, the stored procedure I am having trouble with only does a few SELECTs, and a SELECT INTO a # table --- i.e. no INSERTs, DELETEs, or UPDATEs into a table that other processes can access. There is no BEGIN TRAN or COMMIT TRAN in the code.

    Can "shared read locks" on a table cause deadlocks to occur if another process has created some "exclusive write locks" on another simultaneously needed table? ... i.e. I am thinking that ...

    1: process A is granted "shared read locks" on table 1.
    2: process B is granted "exclusive write locks" on table 2.
    3: process A requests "shared read locks" for the same pages of table 2
    that process B has locked, and has to wait for process B to complete.
    4: process B requests "exclusive write locks" for the same pages of table 1
    that process A has "shared read locks" for,
    and has to wait for process A to complete.
    5: Sybase detects a deadlock and chooses to kill process A.

    I would think that since process A is not using a transaction block (with BEGIN TRAN and COMMIT TRAN) that the "shared read locks" granted for table 1 in step 1 above would be released as soon as the SELECT was finished, thereby allowing process B to be granted "exclusive write locks" for table 1.

    Am I missing something here?

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    sybase provides a sp called sp_lock to uncover who are blocking each other
    yes reads can block in sybase to assure consistency

    consider row level locking if not already done as sybase locks pages by default

  3. #3
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16

    sybase deadlocks

    I think we need a little more information. Do you have access to the server error log? What is the deadlock information is printed in the error log?

    The reason I ask is that you mention that one of the transactions is doing a "select .... into #tablename. Are you deadlocking on tempdb's system tables? If so, I am not sure row locking will help. I have seen this, especially when the "select into" query is a long duration, and also when many processes run "select into #" at the same time.


    If you are not locking tempdb system tables....
    The docs recommend that you make sure the transactions for competing processes lock tables in the same order -- And I have found this to be a good first course of action that often clears up the issue. Also, Check which non-clustered indexes the competing processes are using,

    For details, see the error message write up for error 1205 inthe troubleshooting and error messages guide.


    johndz

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    there is a command which enables detailed logging of deadlock info. Something like SET PRINT DEADLOCK 1 if I remember well.

    You are right ( unfortunately ..= about the possibility of a Select statement causing a deadlock. The shared locks held by Select are indeed released, even before the statement terminates, as the scan slides to the next page. But this assumes the Select is able to read the current page or row. If not, then it waits for process B, and the deadlock can occur.

    You don't even need two separate tables. Currently, I'm fighting a single-table deadlock between one reader and one writer. The reader scans a subset of the table, while the writer updates a few pages in the same subset. They get frequently entangled in a deadly embrace.

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    That is correct, deadlocks occur for deadlocked resources, not necessarily two or more tables.

    The technique for avoiding deadlock is the same for Andrew: Always request/update the RESOURCES (tables in a list of tables, or keys within the same table) in the same sequence. For the latter, that means ensuring that both tasks request the [clustered] keys in ascending, and never in descending or mixed, sequence.

    Tempdb table and system table deadlock can be eliminated by more robust approaches which avoid the substantial overhead of creating/dropping temp tables (as well as the select ... intos that populate them).
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    What is the "substantial overhead" of creating tables in tempdb by using "select into"? What is the difference in creating a tempdb table with "select into" vs "create table/insert into"?

  7. #7
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Kevin,

    assume a datatype conversion (or some other error) happens during "select into #tmp". Sybase rolls back the entire statement, which means the temp table won't exist at all. In order to accomodate this scenario, Sybase does the table-creation in the same transaction as the Select.

    If you have a long-running Select Into #tmp, you can see exclusive locks placed on "sysobjects" and "syscolumns" in tempdb. There are serious doubts whether another process could execute a similar statement at the same time. ( Last time I tested, it couldn't).

    I first heard the story in 1997, but I'm a relative newcomer. Every two years, a Sybase representative claims it has been fixed, but then the concurrency issue pops up again.

    I guess Derek is also alluding to another problem. Consider a scenario with a cursor loop, calling the same stpred proc at every iteration. If the stored proc does a "select into #tmp", then the temp table will be created and dropped repeatedly, perhaps thousands of times. I'm monitoring the locks on a few applications written like this, and at any one moment of time, there is a high probability for me to see locks in tempdb, on sysobjects and syscolumns. For small amounts of data, the create-drop activity takes more time that the actual processing. Sybase does a bit of write-behind, by dropping only _after_ the stored proc returns, but this does not help much.

  8. #8
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    Quote Originally Posted by andrewsc
    Kevin,

    assume a datatype conversion (or some other error) happens during "select into #tmp". Sybase rolls back the entire statement, which means the temp table won't exist at all. In order to accomodate this scenario, Sybase does the table-creation in the same transaction as the Select.

    If you have a long-running Select Into #tmp, you can see exclusive locks placed on "sysobjects" and "syscolumns" in tempdb. There are serious doubts whether another process could execute a similar statement at the same time. ( Last time I tested, it couldn't).
    Well that's just not true and is a long standing (and hard to destroy) myth. The system table locks necessary to create a table are NOT held for the duration of the SELECT statement that populates the table. The execution plan for a SELECT INTO involves 2 steps, where the first step is to create the table. The locks on the system tables are released after this step. The second step is the SELECT which populates the table. The only exception here may be if you have "ddl in tran" set for tempdb which is not a great idea to begin with. If you would like to prove this to yourself, have a look at the output of "dbcc tracone(3604,1212)" and track the locks acquired and released for the system tables.

    Quote Originally Posted by adrewsc
    I guess Derek is also alluding to another problem. Consider a scenario with a cursor loop, calling the same stpred proc at every iteration. If the stored proc does a "select into #tmp", then the temp table will be created and dropped repeatedly, perhaps thousands of times. I'm monitoring the locks on a few applications written like this, and at any one moment of time, there is a high probability for me to see locks in tempdb, on sysobjects and syscolumns. For small amounts of data, the create-drop activity takes more time that the actual processing. Sybase does a bit of write-behind, by dropping only _after_ the stored proc returns, but this does not help much.
    Yes, a different problem altogether, and one that should be avoided. But I didn't read this as the "issue" in Derek's original post. I suspected he had the same assumption as above (locks of system tables for duration of SELECT INTO myth). Maybe I read it wrong.

Posting Permissions

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