December 16, 2003 at 12:49 pm
I am creating stored procedures which are being called from a vbsscript (old code I cannot change now). The vbscrit does open a transaction and creates/inserts into table xyz located in a remote server.
Then one stored procedure is called within the vbscript which requires reading xyz. Due to sql server standard behaviour that select query waits forever.
I have added SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to my procs in order to overcome this issue, but I do generetate SQL with OPENQUERY that is then executed with EXEC(@sql). The sql statement queries tables xyz, and I am explicitely specifying WITH (NOLOCK). My process times out and everything rolls back.
When I run the stored procedure outside the vbscript it runs flawlessly.
Is there any query that can give me enqueue lock information with tablename, sid, sql, etc, maybe joining system tables? In a human readable format?
The GUI is usless because I cannot see the SIDs and what are they doing. It gives me a lock timeout error (while my proc is enqueued).
Thanks
December 16, 2003 at 1:26 pm
sp_who2 and sp_lock may be the command you are lookin for.
quote:
The GUI is usless because I cannot see the SIDs and what are they doing.
What SIDs are you referring to here?
December 16, 2003 at 1:29 pm
Thanks, I will try that.
The SIDs i am referring are the Session IDs on the database and their sql stamements.
December 17, 2003 at 8:04 am
Openquery uses the MSDTC which automatically wraps the statement within a transaction so setting the transaction isolation level will not work. It looks like you are locking yourself out with the vbcode. If you can, testing only, commit the transaction in the vbscript and then execute your stored proc.
I am assuming that when you say your running the stored proc outside of the vbscript and it is running flawlessly, that there are no uncommited transactions on the tables.
You can test this by starting a transaction within query analyzer, do some inserts/updates, do not commit. Open new QA window and run SP. It should time out.
Thanks
Tom
December 17, 2003 at 8:34 am
Thanks alpha3300,
We could not commit before executing the stored proc because the transaction was opened at the top of the vb, and in case of error it will rollback everything. I just arrived and I am making changes, unluckily this one is kinda complex and It will require an overhaul.
We rearranged the order of execution of procs in the vbscript and it finally worked.
I also realized that OPENQUERY opens a new session, as well as EXEC(@sql). I could understand OPENQUERY (because of the remote session ID) but I couldn't believe EXEC did the same thing.
Oscar
December 17, 2003 at 8:37 am
Oh I forgot.
sp_lock will let me know the locks. How can I se which one is enqueued? (waiting for alocked resource)?
December 22, 2003 at 2:42 pm
To see a waiting process, I would look at what sid's are blocked.
eg. Select * from sysprocesses where blocked <> 0
The value in the blocked field is which spid is doing the blocking. With that you can run:
DBCC INPUTBUFFER(spid)
To see what command the blocking connection is running.
Hope that helps.
Tom
December 24, 2003 at 5:42 am
Alpha3300's query will not work when you are waiting for a latch or anything else that is not a lock, as there is no blocking sid.
You need:
select spid, lastwaittype, waitresource from sysprocesses
where blocked=0 and waittype>0
lastwaittype will tell you what kind of block there is. Waitresource will give you the block for some of these types, but sometimes the field is empty.
Here is the explanation for some resources:
Last Waittype Waittype Explanation
* RESOURCE_SEMAPHORE 0x40 Waiting to a acquire a resource semaphore. Used for synchronization.
* DTC 0x41 Waiting on Distributed Transaction Coordinator (DTC).
* OLEDB 0x42 Waiting on an OLE DB provider.
* WRITELOG 0x81 Waiting for log records for a transaction to be flushed to disk.
* PSS_CHILD 0x101 Waiting on a child thread in asynchronous cursor operations.
* EXCHANGE 0x200 Exchange synchronization up for parallel query threads.
* XCB 0x201 Acquiring access to a transaction control block.
Transaction control blocks (XCBs) are usually private to a session, but can be shared between sessions when using the bound session feature or having multiple sessions enlist in the same DTC transaction. Only a single session can have access to the XCB at a time. This waittype likely indicates one session waiting for the XCB while the other session which is holding the XCB resource is waiting on a separate resource.
* DBTABLE 0x202 Only used by Checkpoint process.
* EC 0x203 Killing a connection subthread or Execution Context.
* TEMPOBJ 0x204 Dropping a Temporary Object.
* XACTLOCKINFO 0x205 Waiting on Bulk Operation when releasing\escalating\transferring locks.
* LOGMGR 0x206 Waiting on log writer.
* CMEMTHREAD 0x207 Waiting on access to memory object.
* CXPACKET 0x208 Waiting on packet synchronize up for exchange operator (parallel query).
* PAGESUPP 0x209 Release Spinlock in parallel query thread.
* SHUTDOWN 0x20A Wait for SPID to finish completion before shutdown.
* WAITFOR 0x20B Wait initiated by a WAITFOR command.
* CURSOR 0x20C Waiting for thread synchronization with asynchronous cursors.
Edited by - joachim.verhagen on 12/24/2003 06:40:53 AM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply