Solved

sybase locks, need urgent expert help

Posted on 2004-09-28
8
2,029 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A procedure for exporting installed hotfix details of remote computers using powershell
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

17 Experts available now in Live!

Get 1:1 Help Now