?
Solved

Sybase 10 : I am looking for a script that will show the user, table and row and any more relivent data when the database locks

Posted on 2010-11-12
5
Medium Priority
?
714 Views
Last Modified: 2012-05-10
Sybase 10 : I am looking for a script that will show the user, table and row and any more relivent data when the database locks

thanks !!!!
0
Comment
Question by:NAMEWITHELD12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 34127500
Which Sybase product? I hope you mean ASA 10 (only a few years old) and not Sybase SQL Server 10 (15 years old)... :)
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 34168424
sorry for the delay itis

sybase 10.0.1.3990 ASA
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 34357144
anybody have an idea , how do you throubleshoot DB locks in sybase?
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 2000 total points
ID: 34362428
I have never used that version and I don't have a script. But I think what you need is just query sa_diagnostic_blocking table. I have included definition of that table below. You can find more details at

http://infocenter.sybase.com/help/topic/com.sybase.dbrfen_1001/pdf/dbrfen10.pdf?noframes=true

sa_diagnostic_blocking table

The sa_diagnostic_blocking table is owned by the dbo user, and records blocking events. If logging of
blocking events is enabled, a row is inserted in this table each time a connection is blocked while trying to
access a resource. Typically, this is caused by either a table or a row lock. A large number of blocks may
indicate that you should examine the concurrency in your application to reduce contention for tables and
rows.
There are two versions of this table: sa_diagnostic_blocking, and sa_tmp_diagnostic_blocking.
Columns
Column name Column type Column constraint Table constraints
logging_session_id UNSIGNED INT NOT NULL Foreign key references
sa_diagnostic_cursor.
Foreign key references
sa_diagnostic_request.
lock_id UNSIGNED BIGINT NOT NULL
request_id UNSIGNED BIGINT Foreign key references
sa_diagnostic_request.
cursor_id UNSIGNED BIGINT Foreign key references
sa_diagnostic_cursor.
original_table_object_i
d
UNSIGNED BIGINT
rowid UNSIGNED BIGINT
block_time TIMESTAMP NOT NULL
unblock_time TIMESTAMP
blocked_by UNSIGNED INT NOT NULL
logging_session_id A number uniquely identifying the logging session during which the diagnostic
information was gathered.
lock_id The ID of the lock that caused the blocking if a row or table lock caused the block, otherwise
NULL.
request_id The ID of the request that was blocked if the block did not occur because of a cursor, otherwise
NULL. This value corresponds to the ID assigned to the request in sa_diagnostic_request.
cursor_id The ID of the cursor if the block occurred because of a cursor, otherwise NULL. This value
corresponds to the ID assigned to the cursor in sa_diagnostic_cursor.
original_table_object_id If the block occurred because of a table lock, the ID of the table on which the
block occurred, otherwise NULL.

rowid If the block occurred because of a row lock, the ID of the row on which the block occurred, otherwise
NULL.
block_time The time at which the block occurred.
unblock_time The time at which the block ended.
blocked_by The ID of the connection that held the lock, causing the block.
0
 
LVL 1

Author Closing Comment

by:NAMEWITHELD12
ID: 34414212
awesome
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

765 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