Isolation Level
-
Hi There, Recently I faced an issue regarding deadlock. In the deadlock graph I found the isolation level of the Victim and participant Procedures as serializable. The thing is that the isolation level of the corresponding database is Default Read Committed. How the isolation level changes and will it be one of the reason for the deadlock? Kindly advice me on the same Regards Mahesh "Fall Down To Rise Up!!"
-
Hi There, Recently I faced an issue regarding deadlock. In the deadlock graph I found the isolation level of the Victim and participant Procedures as serializable. The thing is that the isolation level of the corresponding database is Default Read Committed. How the isolation level changes and will it be one of the reason for the deadlock? Kindly advice me on the same Regards Mahesh "Fall Down To Rise Up!!"
Member 11373638 wrote:
How the isolation level changes and will it be one of the reason for the deadlock?
A deadlock occurs when a transaction is waiting for another transaction to finish, where the latter is also waiting on the first. The victim is determined by the priority. First part is identifying the queries that cause the lock in the first place.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Member 11373638 wrote:
How the isolation level changes and will it be one of the reason for the deadlock?
A deadlock occurs when a transaction is waiting for another transaction to finish, where the latter is also waiting on the first. The victim is determined by the priority. First part is identifying the queries that cause the lock in the first place.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy, I did all these findings and altered the procedures. But still I am getting the deadlock. I am just struck with this Serializable isolation. Will it be also a reason for the locks? Regards -Mahesh
-
Eddy, I did all these findings and altered the procedures. But still I am getting the deadlock. I am just struck with this Serializable isolation. Will it be also a reason for the locks? Regards -Mahesh
Member 11373638 wrote:
I did all these findings
Which findings?
Member 11373638 wrote:
and altered the procedures.
What did you alter?
Member 11373638 wrote:
I am just struck with this Serializable isolation.
There is two queries that keep waiting on each other. Once you know which ones, you can check whether they use the default isolation. I don't see a reason why this should be "serialized" and not the default value. Depending on WHAT the queries do, you can define an alternative. If it is a read, then you might opt for a dirty read - or to wait until the other query is finished. If it are two writes, then you'd either need a queue, or a way to specify whom the victim will be. It will never be possible to update a single field using two queries simultaneous, so there will always be a victim unless the logic changes. TL;DR yes, but changing the level will not automatically fix it.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Member 11373638 wrote:
I did all these findings
Which findings?
Member 11373638 wrote:
and altered the procedures.
What did you alter?
Member 11373638 wrote:
I am just struck with this Serializable isolation.
There is two queries that keep waiting on each other. Once you know which ones, you can check whether they use the default isolation. I don't see a reason why this should be "serialized" and not the default value. Depending on WHAT the queries do, you can define an alternative. If it is a read, then you might opt for a dirty read - or to wait until the other query is finished. If it are two writes, then you'd either need a queue, or a way to specify whom the victim will be. It will never be possible to update a single field using two queries simultaneous, so there will always be a victim unless the logic changes. TL;DR yes, but changing the level will not automatically fix it.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy, Yeah your correct, in my case one query was doing a select and other doing a update on the same record. I changed it and thought it will work fine, but i was wrong and this time also deadlock came. And good document related to deadlocks in case of Update and Select in a same table?? One more thins as per your advice changing the isolation level wont help rite!! Regards -Mahesh
-
Eddy, Yeah your correct, in my case one query was doing a select and other doing a update on the same record. I changed it and thought it will work fine, but i was wrong and this time also deadlock came. And good document related to deadlocks in case of Update and Select in a same table?? One more thins as per your advice changing the isolation level wont help rite!! Regards -Mahesh
Member 11373638 wrote:
in my case one query was doing a select and other doing a update on the same record.
That's good; I was afraid you'd be doing two insert/update queries, launching them after another, each in a separate thread. For a read, there's multiple ways to handle the situation, but the server will need to be told what to do. There are isolation levels and query-hints to get the behaviour that you'd want. It'd be a huge coincident if there is always "someone" requesting a record at the moment that the PC is writing it. It is fast in writing a record (and updating the indexes, starting triggers and.. it still takes time) So, best is to identify why someone is always reading after that update. One cause could be that the piece of code that is updating the record, is also fetching it, immediately, simply to refresh the data on the form. In that case it should block/delay the execution until the update-command returns. Even better; don't do the select at all, but refresh using the values that you already have. Is it the same PC/terminal that executes the select after the initial update? You could verify quickly (assuming that the problem is repeatable, is it?) using a trace[^].
Member 11373638 wrote:
And good document related to deadlocks in case of Update and Select in a same table??
I favor MSDN for documentation; besides the isolation levels, you'd also need to see the queries that get executed to determine the current and the desired behaviour. The might already contain or need additional hints[^]. The existing query might already contain a bad idea[^]. And there's a page in the ADO.NET section that may be interesting, even though we are only looking at the database. It's worth the read[
-
Member 11373638 wrote:
in my case one query was doing a select and other doing a update on the same record.
That's good; I was afraid you'd be doing two insert/update queries, launching them after another, each in a separate thread. For a read, there's multiple ways to handle the situation, but the server will need to be told what to do. There are isolation levels and query-hints to get the behaviour that you'd want. It'd be a huge coincident if there is always "someone" requesting a record at the moment that the PC is writing it. It is fast in writing a record (and updating the indexes, starting triggers and.. it still takes time) So, best is to identify why someone is always reading after that update. One cause could be that the piece of code that is updating the record, is also fetching it, immediately, simply to refresh the data on the form. In that case it should block/delay the execution until the update-command returns. Even better; don't do the select at all, but refresh using the values that you already have. Is it the same PC/terminal that executes the select after the initial update? You could verify quickly (assuming that the problem is repeatable, is it?) using a trace[^].
Member 11373638 wrote:
And good document related to deadlocks in case of Update and Select in a same table??
I favor MSDN for documentation; besides the isolation levels, you'd also need to see the queries that get executed to determine the current and the desired behaviour. The might already contain or need additional hints[^]. The existing query might already contain a bad idea[^]. And there's a page in the ADO.NET section that may be interesting, even though we are only looking at the database. It's worth the read[
Eddy, Thanks for your useful information. Both the queries are executed through the same terminal. As per your suggestion I will try to use the hints and lets see whether it really helpful. ;P Regards -Mahesh
-
Eddy, Thanks for your useful information. Both the queries are executed through the same terminal. As per your suggestion I will try to use the hints and lets see whether it really helpful. ;P Regards -Mahesh