Deadlock victim



  • I was a deadlock victim on an online Forum. This is the error I got.  Seems MS SQL is so advanced it can detect deadlocks and decide who the victim is.  I rerun (i.e tried to load the page) and got the same error:

    <font face="Arial" size="2">Microsoft OLE DB Provider for SQL Server</font> <font face="Arial" size="2">error '80004005'</font>

    <font face="Arial" size="2">Transaction (Process ID 77) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</font>

    <font face="Arial" size="2">/500errorLog.asp</font><font face="Arial" size="2">, line 50</font>

     

     



  • And this is a WTF because...?

    Without knowing SQL Server too well, I'd say this is a good thing(TM).

    Googling told me "When a deadlock occurs, by default, SQL Server choose a deadlock "victim" by identifying which of the two processes will use the least amount of resources to rollback."

    Which IMHO, sounds pretty good.



  • Seems simple to me - when two processes are blocking on attempting to acquire resources held by one another, arbitrarily choose one to fail and break the lock. Better than making them both wait forever.



  •  But it did not break the lock. I kept getting the same error.



  • That means the forum software was going through the same idiotic codepath every time, causing the same deadlock. Not a MS WTF.



  • @BiciBella said:

     But it did not break the lock. I kept getting the same error.

     

    So the WTF is that you don't know what a deadlock is, or how a database works, apparently.

    A deadlock involves two processes.  SQL Server resolves it by killing the shortest-running process (that's the victim).  Bad database design can very easily result in several deadlocks in a short time span.  All it takes is some horribly inefficient script queued up a few times.



  • I'd say its the overall design of the website thats causing the deadlocks.



  • It does not have anything to do with me knowing about database, and I probably I know more about it than you, but the end user is presented with such a message, which is wrong and secondly killing the shortest running process did not fix it. So WTF? 

    @Aaron said:

    So the WTF is that you don't know what a deadlock is, or how a database works, apparently.

    A deadlock involves two processes.  SQL Server resolves it by killing the shortest-running process (that's the victim).  Bad database design can very easily result in several deadlocks in a short time span.  All it takes is some horribly inefficient script queued up a few times.



  •  Yeah, this is a site that has been getting updates recently and there were other issues.  



  • @BiciBella said:

    but the end user is presented with such a message

    That's a configuration problem by the site administrator.  A tiny WTF, I suppose, but all too common.  Enjoy your Error'd.

     

    @BiciBella said:

    secondly killing the shortest running process did not fix it

    The point of killing one of the deadlocked processes isn't to "fix" the problem of deadlocks happening (which would seem to be bad DB design or crap code in this case), it's to stop a currently in-process deadlock.  The DB can't prevent retarded code from creating deadlocks (and thus causing the site to fail to load) but it can break deadlocks so the DB can continue working instead of sitting helpless.  A good feature.



  •  I do love the wording when this occurs though

     (In a Vader voice complete with breathing)  You have been <font face="Arial" size="2">chosen</font> as the deadlock victim...

     

    Thaaat's iiimmmposssiiibble



  • @BiciBella said:

    It does not have anything to do with me knowing about database, and I probably I know more about it than you,
     

    Wow, you can tell how much he knows about database(s) just from that post? Wow!  Do me next!



  • Man I hate that error. I seem to get it all the time using LINQ and asynchronous/multi-threaded programming. 


  • Garbage Person

     @Dudehole said:

    Man I hate that error. I seem to get it all the time using LINQ and asynchronous/multi-threaded programming. 

    Because you suck at it. Here's a hint - you can't just go willy-nilly multithreading things.

     

    (See children? This is why we have multicore, 64bit processors and every fucking application on earth is still only 32bit/singlethread)



  • This is an example of poor programming. Not of the DB but of the forum application. Can't think how a read messages/write a new message application could have a deadlock situation but it is an expected condition. The application code should back off and restart what it's trying to do. The application was probably written by an amateur.



  •  You have been sacrified! Are you a virgin?



  • @Weng said:

    (See children? This is why we have multicore, 64bit processors and every fucking application on earth is still only 32bit/singlethread)
     

     64-bit has 0% correlation with multi-threading. 64-bit doesn't make programming harder. The reason most apps are still 32-bit is simple: 95% still have a 32-bit OS installed and a 32-bit app runs on a 64-bit machine but not the other way around. Don't worry though, it will change. We saw that with Win95.



  • @Weng said:

    Because you suck at it. Here's a hint - you can't just go willy-nilly multithreading things.

     

     

    Honestly, people are saying that it's crap code, retarded design, whatever, but if you've ever worked with a large-scale application you'd know that it's really, really hard to prevent deadlocks.

    There are several different kinds of deadlocks and some of them can be caused by the most simple, ordinary, concise sets of transactions.  All it takes is for two transactions to write to the same two tables at the same time, but in opposite order, and boom, you're deadlocked.  Or worse, the transactions can be making these updates on just one table but on different rows that happened to share pages.  It's a frustratingly common scenario in high-transaction environments.

    Sure, there are rules of thumb you can follow to reduce deadlocks - keep your transactions short and small, do your SELECTs before beginning the transaction if the data is unlikely to change, avoid cursors, make use of READ UNCOMMITTED and NOLOCK, and so on and so forth, but there are going to be times when you have to break some of these rules.  You'll have some absolutely critical transaction that must run and takes a long time to run and is highly sensitive to any external changes, and all you can do is either use an applock or just let the deadlock happen and handle it in the code.  In a lot of production systems you'll likely see the latter in several places.

    You could say that it's a WTF not to handle the deadlock, but SQL deadlocks by nature generally happen where you least expect them, because the long-running transaction that's really responsible for the deadlock is the one that's allowed to continue running; the victim is the simple transaction where a deadlock seems unlikely.  Unless you write deadlock-handling code for every single SQL statement in your entire app, there's always a (small) possibility that what appears to be some completely random code path will fail for that reason.

    And incidentally, very little of this has anything to do with multicore machines or multi-threaded applications.  You can have a completely single-threaded application and still end up with a SQL deadlock if there are multiple users.  If you really try, it's actually possible to create a SQL deadlock on a single-user, single-threaded app (there's nothing stopping you from firing up two simultaneous connections and forgetting that one of them is in the middle of a transaction).



  • @Aaron said:

    @Weng said:

    Because you suck at it. Here's a hint - you can't just go willy-nilly multithreading things.

     

     

    Honestly, people are saying that it's crap code, retarded design, whatever, but if you've ever worked with a large-scale application you'd know that it's really, really hard to prevent deadlocks.

    [...snip...]

     

    True, but since this was about an online forum it's a little more likely that is stupid code than that it really is unpreventable.

    @Aaron said:

    And incidentally, very little of this has anything to do with multicore machines or multi-threaded applications. 

    True, but from deadlocks it's just a little leap to multi-threading.



  • A somewhat-related WTF: deadlocks made me think of the (in)famous three-interlocked-gears arrangement and I went looking for pictures and found an awesome WTF buried in this page: The Gears Represent What Now?



  • @too_many_usernames said:

    A somewhat-related WTF: deadlocks made me think of the (in)famous three-interlocked-gears arrangement and I went looking for pictures and found an awesome WTF buried in this page: The Gears Represent What Now?

     

    Hah, good one.  I guess they represent a deadlocked team.

    Apparently this is a popular theme in stock photos.



  • I assume you have experience with programming LINQ in an asychronous environment that you would like to share? And what's 64-bit support got to do with anything that isn't in an HPC/datacenter environment.

    (P.S., you come off as an asshole)



  • @too_many_usernames said:

    A somewhat-related WTF: deadlocks made me think of the (in)famous three-interlocked-gears arrangement and I went looking for pictures and found an awesome WTF buried in this page: The Gears Represent What Now?

    A symbol for 'team play'.... o.o



  • @Nyquist said:

    @too_many_usernames said:

    A somewhat-related WTF: deadlocks made me think of the (in)famous three-interlocked-gears arrangement and I went looking for pictures and found an awesome WTF buried in this page: The Gears Represent What Now?

    A symbol for 'team play'.... o.o

    To be fair, many teams [b]do[/b] operate like that. Well, "operate" may be overstating it.


Log in to reply