Re: unexpected deadlocks

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 18:04:24 -0500

The second alternative requires more reading, even if it is using an index.

That said, is there a compelling business reason to have no gaps in the
generated sequence number? Right now, you're serializing things, which will
hurt scalability. 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com
.
"Henk Kok" <henk@mira_uknowwhat2do_beau.nl> wrote in message 
news:4212153b$0$28980$e4fe514c@news.xs4all.nl...
Hi Tom,
I'm not sure what you had in mind, but I can now answer the question "why
are the deadlocks not surprising" myself. The application runs under COM+
and COM+ transactions, which force the Sql Server transaction isolation
level to serializable.
See also: http://vyaskn.tripod.com/com_isolation_level.htm
This explains the deadlocks. I've changed the procedure using your
suggestion. I've created 2 alternate solutions:
1. still use the [System] table in the way that Tom described; both the
update/assign and the insert in 1 transaction
2. instead of using the [System] table I use "select max(MutatieID) + 1 from
messages with (UPDLOCK)" which will use an optimized index search
I'm a bit worried about locking the index in the second altenative, but
we'll test both. Any thoughts on this remain very welcome.
Henk Kok

Relevant Pages

  • Re: Transaction Deadlocks your thoughts?
    ... The rules for minimizing deadlocks are ... you to "soft lock" any resource you need prior to going into transaction ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Deadlock
    ... RAISERROR 20000 'trg_myTable_UPDATE Update Trigger Failed. ... COMMIT TRANSACTION ... As for why you are getting deadlocks, ... SQL Server error log. ...
    (comp.databases.ms-sqlserver)
  • Re: How can I minimize deadlocks ??
    ... You can do this without a transaction: ... Columnist, SQL Server Professional ... Select Top 1 @DocID = doc.DocID ... we are experiencing quite a high number of deadlocks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Using MARS with JDBC Driver
    ... > was blocked on the SELECT by another transaction that was blocked on the ... You mentioned something about server side cursor not scaling the way you ... > deadlocks. ... > may occur after acquiring an UPDATE lock, because we know that we have ...
    (microsoft.public.sqlserver.jdbcdriver)