December 11, 2002 at 5:06 am
Hello all,
We are currently running sql server7 which gets updated using agents in Outlook and Gets accessed from the web using either ASP or coldfusion. We currently have mulpitle web applications running of the one Server server machine.
We have noticed that some of the data being recieved into Outlook is never getting entered into the SQL Database. We think this may be due to locking or Deadlocks as it only seems to be in the morning during heavy traffic that this happens.
I know trace can be performed for deadlocks but will this highlight any other problems such as locks or how can these be found?
Also is an insert flagged as completed before a trigger (for an insert on that table) starts or when the trigger has finished.
Thanks,
Sean Martin
December 11, 2002 at 5:14 am
With regard to your question about the Insert trigger...
The trigger will fire before the Insert is committed since it is permissable to rollback the insert operation should the inserted record(s) not meet some criteria specified in your trigger. This is how triggers can be used to maintain referencial integrity.
December 11, 2002 at 5:36 am
If you knwo roughly when it is occurring everyday do a general trace on SQL statments begining with Profiler. This way you can get the order of events occurring that lead up to the issue. You may always want to catch exceptions to see if it is not a deadlock but something else.
December 11, 2002 at 8:38 am
I have done a Lock trace and have lots of
Lock: Timout for a certain database, they have an Event Sub class of share. Does this mean that the records these locks timeout for are not getting entered?
The locktinmeout is set to -1, dosen't this means I should NOT be getting lock timeout??
Edited by - sean_martin on 12/11/2002 08:41:10 AM
December 11, 2002 at 9:39 am
I believe the LockTimeout setting is connection based. Could there be connections that set it to something else? The default is -1 so it would have to be explicitly set to another value.
Is the database ID for the lock timeouts captured in profiler ever 2, which is TempDB? I've noticed in the past with applications that make heavy use of TempDB there will be lots of Lock Timeouts.
There are two things I do in profiler to investigate lock timeouts:
1. Capture the ObjectID in Profiler to find out what is being timed out on. The ObjectID will be included in the Timeout event in Profiler.
2. Capture SQL:StmtStarting events to find the last statement a SPID executed before a Lock Timeout event occurred for that SPID. SQL:StmtStarting events collect a lot of data so there is a bit more overhead. You can also do this with the Deadlock event in Profiler.
December 12, 2002 at 3:30 am
After running profiler I see that that a lot of lock:timouts are happening for TempDb but we never use this database.
Do views hold their data within the tempDb database?
Our SQL server also is a bit stressed at the minute would this cause the problem? Still waiting for the replacement to arrive.
There are also loads of lock timeouts happening on our main Database as well as the TempDB. When a lock timeout does this data not get entered if its an insert.
There seems to be two main tables affected, one gets records inserted from a VB agent and uses a trigger to update the other table. These inserts would be happening all the time but the VB agent is sequential so two inserts never get done at once.
These two tables are also the main two tables that would be accessed from the Web. so select statements would be frequent.
Deadlocks are also occurring. Is there any special commands need to be added to the select or update statements to insure Locks or deadlock do not happen?
E.g “SELECT * from TABLENAME (UPDLOCK) where id = @id “ to lock this record for an update!
Thanks for all the Previous help,
Slowly I am seeing through the MUD
Sean Martin
December 12, 2002 at 9:19 am
I'm not sure about Views using TempDB to store data, but I know tables created with the # or ##, Order by operations, large Hash joins and some cursors use TempDB to store temporary results. Try to limit these operations. Make sure your TempDB files are large enough and not auto-growing during execution if possible.
I don't know of any special commands to add to a query that prevent lock timeouts or deadlocks from happening. If you want to read "dirty" data you can put NOLOCK hints or set the transaction isolation level to Read Uncommitted.
The only that I can suggest is to find the SQL:StmtStarting events that are causing the lock timeouts and deadlocks and modify the transactions that house these statements. You may have transactions that have lots of unrelated statements in them that do not really need to be in a transaction? Just a thought...
If modifying the transactions isn't an option then investigate ways to make the queries that are under a high amount of contention run faster by indexing or general query optimization.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply