cancel
Showing results for 
Search instead for 
Did you mean: 

Deadlock, waiting for a 'next key'

0 Kudos

Bonjour,

   I'm having a deadlock that comes along every once in a while, not a big issue but there is something I try to figure out and I am not quite sure what to make of it.

The deadlock is a result between an UPDATE WHERE (Process 1) and a SELECT (Process 2).

The following has been retrieve from the log :

  Deadlock Id 1: Process (Familyid 0, Spid 4093) was
waiting for a 'next key' lock on row 0 page 12282632 of the 'tom_req_assign'
table in database 'tom_data' but process (Familyid 0, Spid 4651) already held a
'shared row' 'range' lock on it.

It is the part where you can read : "was waiting for a 'next key' lock" that is bugging me.

Am I correct if I understand this as : "I am waiting to get a lock on the next value I need to update" ?  Or is there something else to understand?

Also, trying to minimize the occurrence of this deadlock, would it be a good idea, since I need to update many records, to acquire a table lock before updating my records.  From my point of view, I feel it is a good idea, what your thoughts?

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

I do not know how this could be ISOLATION 3.  From what I read, ISOLATION is set at the server level, session level ou query level.  The serveur default isolation level is 1, there is no specific isolation level set at the session level and the query does not, in any way set a different isolation level.

Could Sybase change the level of isolation based on the query?

Further, when Sybase docs refers to Isolation Level "next-key" it is always unsing a dash in between the two words but not in the deadlock log (might be a consistency "error" though)?

jong-un_seo
Participant
0 Kudos

Hi William-Paul,

Are there applications develped using ESQL-C?
If so, please check its precompiler(cpre or cpre64) option whether "-r" option specified or not.
If you didn't specify it, it will run as isolation 3.
ESQL is setting isolation level 3 as default.
You can set it from ocs.cfg at $SYBASE/OCS-15_0/config too.

Also you can check isolation level of a specific spid like below.

select @@isolation

go

OR

select pssinfo(<spid>, 'isolation_level')
go

Thanks,
jseo

0 Kudos

I do not know about ESQL-C but I will sure find out.

As for the default isolation level, it is definitely 1.

1> select @@isolation
2> go
            
-----------
           1

(1 row affected)

I will monitor the processes when they will be next lauched to see if the spid is running at the expected isolation level.

I thank you verry much for your time and expertise Jong-un.  I will keep near the commands you provided.

Best regards

jong-un_seo
Participant
0 Kudos


Hi William--Paul,

It's related to isolation level 3.
Please check it from SyBooks Online

Thanks,
jseo