Solved

sybase locks, need urgent expert help

Posted on 2004-09-28
8
2,040 Views
Last Modified: 2012-05-05
Hey i need some clarification and help on sybase regarding locks. We use ase12.5

1) select:  what type of lock if any does this use?  
2) update: row level lock?  
3) delete:  row level lock?
4) begin transaction .. end transaction:  table lock?
5) is there a history log somewhere we can look exactly what happened that caused a deadlock?

we are getting deadlock error during heavy load from the following stored proc. However it just does single updates, so i dont understand how it could possibily cause a deadlock. This is very urgent, thank you for your help!

create procedure StoredProc
as

declare @statcount int
declare @eventid numeric(9,0)


declare eventidcursor cursor for
  select EventId from TableOne where Stat = "somevalue"      
for read only

open eventidcursor
fetch eventidcursor into @eventid
 
while @@sqlstatus = 0
begin

select @statcount = (select count(*) from TableTwo where EventId = @eventid and Stat not in ("somevalue","somevalue2"))

if @statcount = 0
begin
  if (select distinct istrue from TableThree where EventId =  @eventid) = "Y"
    begin
       update TableOne set Status = "somevalue", lastUpdTime = getdate(), lastUpdUser = "systemengine",      EventVersion=EventVersion+1 where EventId = @eventid      
    end
  else
    begin
       update TableOnet set Status = "somevalue", lastUpdTime = getdate(), lastUpdUser = "systemengine", EventVersion=EventVersion+1 where EventId = @eventid      
    end
   end

fetch eventidcursor into @eventid
end /*end of while loop */
       
close eventidcursor
deallocate cursor eventidcursor

select EventId from TableOne where Status = "somevalue"

return

this is the exception we logged:

********************************* Sep272004 16:30:49:227 BEGIN *****************************
java.rmi.UnmarshalException: Failed to marshal error response: 'com.sybase.jdbc.SybSQLException: Your server command (family id #0, process id #648) encountered a deadlock situation. Please re-run your command.
' because exception ; nested exception is:
        java.io.NotSerializableException: com.sybase.jdbc.SybResultSet
        at weblogic.rjvm.BasicOutboundRequest.sendReceive()Lweblogic.rmi.spi.InboundResponse;(Optimized Method)
        at weblogic.rmi.internal.BasicRemoteRef.invoke(Ljava.rmi.Remote;Lweblogic.rmi.extensions.server.RuntimeMethodDescriptor;[Ljava.lang.Object;Ljava.lang.reflect.Method;)Ljava.lang.Object;(BasicRemoteRef.java:138)
        at weblogic.jdbc.rmi.internal.CallableStatementImpl_weblogic_jdbc_wrapper_CallableStatement_com_sybase_jdbc_SybCallableStatement_812_WLStub.executeQuery()Ljava.sql.ResultSet;(Unknown Source)
        at weblogic.jdbc.rmi.internal.CallableStatementStub_weblogic_jdbc_rmi_internal_CallableStatementImpl_weblogic_jdbc_wrapper_CallableStatement_com_sybase_jdbc_SybCallableStatement_812_WLStub.executeQuery()Ljava.sql.ResultSet;(Unknown Source)
        at weblogic.jdbc.rmi.SerialCallableStatement_weblogic_jdbc_rmi_internal_CallableStatementStub_weblogic_jdbc_rmi_internal_CallableStatementImpl_weblogic_jdbc_wrapper_CallableStatement_com_sybase_jdbc_SybCallableStatement_812_WLStub.executeQuery()Ljava.sql.ResultSet;(Unknown Source)
        at ReceivePrice.go(Ljava.lang.String;)V(Optimized Method)
        at BaseEngine$RemindTask.run()V(BaseEngine.java:59)
        at java.util.TimerThread.mainLoop()V(Timer.java:432)
        at java.util.TimerThread.run()V(Timer.java:382)
        at java.lang.Thread.startThreadFromVM(Ljava.lang.Thread;)V(Unknown Source)
Caused by: java.io.NotSerializableException: com.sybase.jdbc.SybResultSet
        at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1054)
        at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1332)
        at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1304)
        at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1247)
        at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1052)
        at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:278)
        at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(ChunkedObjectOutputStream.java:116)
        at weblogic.rjvm.MsgAbbrevOutputStream.sendThrowable(MsgAbbrevOutputStream.java:387)
        at weblogic.rmi.internal.BasicServerRef.handleThrowable(BasicServerRef.java:594)
        at weblogic.rmi.internal.BasicServerRef.postInvoke(BasicServerRef.java:549)
        at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:435)
        at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:30)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
--------------- nested within: ------------------
weblogic.rmi.extensions.RemoteRuntimeException: Unexpected Exception - with nested exception:
[java.rmi.UnmarshalException: Failed to marshal error response: 'com.sybase.jdbc.SybSQLException: Your server command (family id #0, process id #648) encountered a deadlock situation. Please re-run your command.
' because exception ; nested exception is:
        java.io.NotSerializableException: com.sybase.jdbc.SybResultSet]
                                                                                                           1,1           Top
0
Comment
Question by:gagaliya
8 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12175205
1) select:  what type of lock if any does this use?  

    will use a shared intent lock

2) update: row level lock?  

    depends on the table's locking scheme. ALL PAGES, DATA PAGES or DATA ROWS

3) delete:  row level lock?

    depends on the table's locking scheme. ALL PAGES, DATA PAGES or DATA ROWS

4) begin transaction .. end transaction:  table lock?

    no, begin tran will not force a table lock, but will hold all locks until committed

5) is there a history log somewhere we can look exactly what happened that caused a deadlock?

    a brief expanation of what cased the deadlock can be error log in $SYBASE/ASE/install
    including the code being executed by the processes involved

0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12177645
As ChrisKing wrote - check your error log. If you don't see any deadlock information there, you have probably switched off printing deadlock information into log - use ' sp_configure "print deadlock information", 1 ' to switch it on and wait for another deadlock.
0
 
LVL 1

Author Comment

by:gagaliya
ID: 12180910
thanks for the response, all of our tables are in ALL PAGES lock setup, maybe that could be the problem.
Should i change it to DATA ROWS locks, are there any impacts with this change besides performance?

This is the exact error dba gave me from our db. I still dont understand why it caused a deadlock. It looks
like two stored proc both are trying to update this one table. So why is this a deadlock? shouldnt one just
wait until the other finishes?

Deadlock Id 5: detected. 1 deadlock chain(s) involved.
 
Deadlock Id 5: Process (Familyid 452 452) (suid 2294) was executing a UPDATE command in the procedure 'StoredProc1'.
SQL Text: StoredProc1
Deadlock Id 5: Process (Familyid 548 548) (suid 2294) was executing a UPDATE command in the procedure 'StoredProc2'.
SQL Text: StoredProc2
Deadlock Id 5: Process (Familyid 0, Spid 548) was waiting for a 'update page' lock on page 710 of the 'TableOne' table
in database 13 but process (Familyid 452, Spid 452) already held a 'update page' lock on it.
Deadlock Id 5: Process (Familyid 0, Spid 452) was waiting for a 'exclusive page' lock on page 4459 of the 'TableOne' table
in database 13 but process (Familyid 548, Spid 548) already held a 'shared page' lock on it.
 
Deadlock Id 5: Process (Familyid 0, 548) was chosen as the victim. End of deadlock information.
 
03:00000:00581:2004/09/28 16:32:03.00 server  DBCC TRACEON 8399, SPID 581
01:00000:00195:2004/09/28 16:33:25.94 server  Deadlock Id 6 detected
Deadlock Id 6: detected. 1 deadlock chain(s) involved.
 
Deadlock Id 6: Process (Familyid 452, 452) (suid 2294) was executing a UPDATE command at line 1.
SQL Text: update TableOne set Status = 'somevalue', lastUpdTime = getdate(), lastUpdUser = 'somevalue' where EventId =470
Deadlock Id 6: Process (Familyid 195 195) (suid 2294) was executing a UPDATE command in the procedure 'StoredProc2'.
SQL Text: StoredProc2
Deadlock Id 6: Process (Familyid 0, Spid 195) was waiting for a 'update page' lock on page 710 of the 'TableOne' table
in database 13 but process (Familyid 452, Spid 452) already held a 'update page' lock on it.
Deadlock Id 6: Process (Familyid 0, Spid 452) was waiting for a 'exclusive page' lock on page 4459 of the 'TableOne' table
in database 13 but process (Familyid 195, Spid 195) already held a 'shared page' lock on it.
 
Deadlock Id 6: Process (Familyid 0, 452) was chosen as the victim. End of deadlock information.

0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 300 total points
ID: 12181081
apart from performance you need more locks and therefore another hit on performance.

If you are considering changing the locking scheme DO NOT just go an change everything. Pick the table of the highest contention and start with that.
Monitor the locks / contention for several days before looking at a second, if no improvement lokk at moving it back to ALL PAGES.

Look as sp_sysmon output, it can recommend some table and other changes that can help (lots ob output)
it is not a piece of cake, there are even courses on this http://www.sybase.com/detail?id=1017856

the fundermentals of a deadlock are:
- I have locked A and now want B
- You have locked B and now want A
we both cant win, one transaction must die (usually the one with the lowest I/O)

this transaction can span over several statement in a procedure or script but is usually found to be something like an update of another table in a trigger


hapy hunting
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Assisted Solution

by:grant300
grant300 earned 100 total points
ID: 12183108
Deadlocks can usually be avoided by making sure that all portions of an application lock data items in the same order.

For instance, if you have five tables, A, B, C, D, and E, then you need to look at the application logic that does updates
and determine the "natural" order that updates (and therefore) locks will be applied.  Then go through all of the
application code that does updates (or deletes and sometimes inserts too) and make sure that the lock application order
is always the same.  This pretty much eliminates the classical deadlock scenario that ChrisKing spelled out above.

You can start by looking at (and/or posting) StoredProc1 and StoredProc2.  The problem will most likely be pretty
obvious..

BTW, I would rewrite the stored proc you posted as an update with corellated subqueries rather than using cursors.
I think you will find you get better performance that way and the code (if not the SQL) is simpler and easier to
maintain.

Bill
0
 
LVL 13

Assisted Solution

by:alpmoon
alpmoon earned 100 total points
ID: 12186275
I think the main problem here is the application starts a transaction when it runs StoredProc. Otherwise Sybase doesn't hold locks when you send individual select and update statements in a stored procedure. To avoid it you must check documentation and setting of the application (I think it should be part of weblogic). I am sure that you wouldn't experience any problem if you run that stored proc directly because you don't start any transaction in the proc.

There should be a parameter to start a transaction automatically in the application and you need to turn it off.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12188186
the setting that alpmoon is talking about is "auto commit"
to test what mode you are in, run the following:

IF @@tranchained = 1
    PRINT "Chained Mode"
ELSE
    PRINT "Auto Commit Mode"

auto commit is the default mode for new servers
0
 
LVL 1

Author Comment

by:gagaliya
ID: 12202562
thanks for the suggestions guys, i did some more debugging and changed the primary table(that one that caused deadlock) to datarow from allpage locking.
That seem to solve the problem, as the two stored proc will never access the same rows. I cant really enforce the table order due to business logics.

for my own reference, those are good future changes i should make:

1)change the update from cursor to single update with subq
2)move the begin transaction below the select statement

thanks again!
gaga
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now