Solved

sybase locks, need urgent expert help

Posted on 2004-09-28
8
2,051 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
This article outlines why you need to choose a backup solution that protects your entire environment – including your VMware ESXi and Microsoft Hyper-V virtualization hosts – not just your virtual machines.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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