on 01-12-2015 4:14 PM
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
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)?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.