Jump to content


Photo
- - - - -

SQL Server Catch Deadlocks


3 replies to this topic

#1 Sean Graves

Sean Graves

    Member

  • Members
  • PipPip
  • 13 posts
  • Gender:Male
  • Location:United Kingdom

Posted 20 May 2011 - 04:32 PM

In proiv 5.5 is there a way that you can trap for deadlocks in sql server ?

In theory I suppose you can suppress errors, but would that then give you a bad write logic?

#2 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 20 May 2011 - 06:27 PM

Sean,

Move to 6.2. SQL Server locks can be handled quite elegantly with no delay in the notification to user that a table is locked.

Lew

#3 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 21 May 2011 - 12:28 AM

Lew,

What's different about SQL Server record locks in 6.2 / 7.0?

Regards

Sean,

Move to 6.2. SQL Server locks can be handled quite elegantly with no delay in the notification to user that a table is locked.

Lew



#4 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 21 May 2011 - 08:01 AM

Hi Joseph,

Haven't spoken in a while. Nice to see you on.

In version 5 we had to resort to SQL Timeout for handling of SQL Server locks. This caused a long running transaction to sometimes be treated as a locked record. In 6.2 that is no longer the case. We can now get instantaneous notification of a SQL lock without resorting to SQL timeout. By adding some views you can get at the user locking the record and display that info to user encountering the lock (although we did that in version 5). Having to employ SQL Timeout was a major headache for us in version 5. I had discussions with Mark Dexter about this very topic. At the time, they were employing SQL Timeouts as well. What version/build this better handling of SQL Server locks was instituted I am not sure. It may even be in some of the later 5.5 builds. We jumped on version 6 pretty early.

Here is a post from Ross Bevin on this topic among others:
http://www.proivrc.c...?showtopic=4067

Here is a post from me with additional information:
http://www.proivrc.c...record-locking/

Now many of the settings are not new. They have been around for a while but they did not work properly with SQL Server. The SQL locking issues I speak about were specific to SQL Server only. In Ross's post it appears the PROIV lock notification improvements for SQL Server happened in 6.1

Sean - in your post you were asking about deadlocks. I answered about record locks. Over the years, we have had only a handful of deadlocks where SQL Server chose the victim to abort - so not much of an issue for us. I don't remember if a deadlock raises an error 366 or not but you can fire off your own error screen to handle it. By employing a custom error screen, you won't have to add code to each read with error logic and you can make the presentation nicer for the user.

Lew

Edited by Lewis Mccabe, 21 May 2011 - 08:02 AM.




Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users