Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-21 : 00:28:09
|
| Viking writes "I have a table called say "XYZ" which gets populated with records (from RADIUS) every second in numbers of 15 - 20. U shd have guessed it by this time, the total no. of records by the end of the day and grows precisely in number itz whopping 3.75 - 5 lacs..! But, each record in the table is processed by few stored procedures.. and are marked processed.. once processed.. Seeing the bulk of table I had decided to move these processed marked records to a diff table in order to reduce the bulk for the next dayz operation...!! Now, when i do this, I get errors by the RADIUS saying that unable to insert.. and loose a max. of 100 records..!! to be inserted into the sql server 7.0. in the event viewer. I debugged and found that i get one often occurring error :state = 40001, error = 1205, [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #9) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. I understand the above error as I move the previous days processed records to diff. table by inserting into temp table and deleting the moved records from the parent table based on a LogID which is an identity, non-clustered index column. After digging lotta thro' with the above problem, I was told by MS that i have some problem with the procedure during the moving of the above mentioned processed records as per requirement. But, I don't c ne problem .. ..!!! Put all my senses into it..but in vain. Could ne body help..!!EXEC ('INSERT INTO ' + XXX + ' EDATE,ETIME,LOGTYPE,LOGIN,SESSIONID,USAGE,INBYTES,OUTBYTES,PROCESSED_FLAG,LOGID) (SELECT EDATE,ETIME,LOGTYPE,LOGIN,SESSIONID,USAGE,INBYTES,OUTBYTES,PROCESSED_FLAG,LOGID FROM DBO.XYZ WHERE CONVERT(CHAR,EDATE,102)=CONVERT(CHAR,GETDATE(),102) AND PROCESSED_FLAG=''Y'')' )EXEC ('DELETE DBO.XYZ FROM DBO.XYZ INNER JOIN ' + XXX + ' ON DBO.XYZ.LOGID=' + XXX + '.LOGID')Thanx in advance." |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-03-21 : 03:07:36
|
| OK - basically your problem is that the records you specifically want to delete are being locked. If you run your proc, and then run the SP_WHO2 procedure you should see in the 5th column (BlkBy) who is locking out your procedure ...Can you schedule your procedure for later night/early morning to avoid other users, or is this a 24x7 envirnoment? If not, you may need to schedule this so that you are given a window of time to work in.Alternatively, you may be able to do something based on time. I see you have edate and etime. Perhaps you can try to process records that are older, first. Perhaps something along the line of etime <= time - 1 hour ?HTHEdited by - wanderer on 03/21/2002 03:08:27 |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-21 : 08:37:30
|
| The order of your dml statements suggests that you are encountering a cycle deadlock.To verify this, run SQL Profiler, select "Locks" from the event list, and monitor the lock:deadlock and lock:deadlock chain events. When the exception occurs you should see the chain in the trace data.To take a pure guess, I would suspect a significant factor in the deadlock is the inability of the optimizer to use an index in the select statement, where (it looks like) you are using convert to check if edate = (today).A much better way to do this is to place a clustered index on edate and instead use datediff( dd, edate, current_timestamp) = 0. Try it and compare the execution plans.setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
Problem with Page Locking