key set cursor error in job

Question

key set cursor error in job

SQLArch

Ten Centuries

Points: 1322
More actions
October 1, 2004 at 9:43 am

#84208

I am getting this error in the job.
The job runs fine most of the days but it confilts on some days with this error
Msg 0, Sev 0: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]
Msg 1205, Sev 13: Transaction (Process ID 54) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001]
Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000]
The job is running a stored procedure which has read only fast forward cursor but doesn't have keyset cursor.
Where to look for errors.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Douglas De Ivey Old Hand Points: 396 More actions · Answer 1

Hi,

The important word here is "deadlock". These can be tricky to track down and eliminate but there is lots of help in Books Online particularly the article on "mimimizing deadlocks" and "toubleshooting deadlocks"

You need to be thinking about what your procedure is locking and either making the transactions shorter, changing the order of the updates or, if the job only runs for a few seconds, lock everything before you start the updates.

If you can give me a few more details about the procedure, I'll be able to be mroe specific.

Even better, ditch the cursor they are rarely the best solution.

Wesley Brown SSChampion Points: 12045 More actions · Answer 2

Deadlocks are simple to explain and usually complex to fix.

the issue is table A is needed for a transaction by thread 1. thread 2 starts and grabs table b then thread 1 wants table b and thread 2 wants table A Poof! deadlock city, SQL Server has to choose which thread must be killed in order for at least one process to finish. You need to inforce the order in which tables are modified to keep deadlocks from happening.

SQLArch Ten Centuries Points: 1322 More actions · Answer 3

I am running gaint cursor to see what values and assets need to be reconciled from account, price and assets. then checking market value if exists then insert values in market value table. at end delete the market values if no position exists for accounts and assets.

If I am using market value table in cursor and then later updating and deleting using other nested procs. Can this cause deadlock.

The cursor is read only fast forward and should not conflict with update or delete.

declare processMV cursor local fast_forward read_only

for

select a.AcctIdNmb

, a.SecurityIdNmb

, min(

case

when a.StartDT < '1/1/1900' then cast('1/1/1900' as datetime)

else a.StartDT

end

&nbsp as StartDT

, mvl.ValueDT

from (

-- New accounts query

union -- new prices query

union marketvalue status changed query

---other unions

fetch from cursor

while (@@fetch_status = 0)

calculate load by asset count and insert into market value

delete from market value where no positions.

loop

end

Also marketvalue table is used from web for some reports, need read only there.

where do its deadlocking, not sure.

---

Wesley Brown SSChampion Points: 12045 More actions · Answer 4

This is being deadlocked by another process. Like a front end application

Douglas De Ivey Old Hand Points: 396 More actions · Answer 5

I’ve done a couple of tests with cursors using a query based on your template. For the time that the open cursor is executing(which could be a while if the query is long), the server holds exclusive table locks on the tables involved (it seems a bit excessive to me but it's what my server did!). As you have what looks like a complex query, it could be the source of your deadlocks especially as the u8nion means that locks may ba acquired over a period of time. (but remember there are always 2 processes involved in a deadlock so this job is not the only culprit)

I think I would try the following quick fix so see if it helps.

Rather than build the cursor on the complex query, try creating a work table, then inserting the records from the complex select into the work table, and finally, building the cursor on the work table. Doing this should prevent the long locks on the tables and hopefully eliminate the deadlocks.

You will also be able to insert into the work table using two or three separate inserts rather than doing a union which will also reduce the change of deadlocks.

Of course, it might not be the cursor causing the deadlock, but the called stored procedure(s). You might want to run the profiler on the job or add some ‘trace’ statements to work out exactly where the deadlock occurs.

You can also turn on the trace flag (see Books Online) to get more info about the deadlocks.

Finally it is interesting that it is only this job that is failing because by definition there are always at least two processes involved in a deadlock so you would expect ‘the other one’ to fail sometimes.