Problem persist

Problem persist

Post by Peter A. Smirnof » Thu, 26 Dec 2002 22:53:45




Subject: Both methods failed
Date: 25 ????? 2002 ?. 11:49

Hi again,

Both methods, INSERT\SELECT and UPDLOCK
are failed. Deadlock presents. I rewrite  proposed code as stored procedure
called "ins_rng3". I test it like this :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE



begin



end

One of two concurrent transaction is successfully completed. Another is
failed with message:
"Transaction (Process ID 56) was deadlocked on lock resource with another
process and has been chosen as the deadlock victim. Rerun the transaction"

Whats wrong?

Thx in adv,
Peter

 
 
 

Problem persist

Post by Uri Diman » Thu, 26 Dec 2002 23:08:58


Peter
To help minimize deadlocks:
Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.




> Subject: Both methods failed
> Date: 25 ????? 2002 ?. 11:49

> Hi again,

> Both methods, INSERT\SELECT and UPDLOCK
> are failed. Deadlock presents. I rewrite  proposed code as stored
procedure
> called "ins_rng3". I test it like this :

> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE



> begin



> end

> One of two concurrent transaction is successfully completed. Another is
> failed with message:
> "Transaction (Process ID 56) was deadlocked on lock resource with another
> process and has been chosen as the deadlock victim. Rerun the transaction"

> Whats wrong?

> Thx in adv,
> Peter


 
 
 

Problem persist

Post by Erland Sommarsko » Fri, 27 Dec 2002 03:13:20



> Both methods, INSERT\SELECT and UPDLOCK are failed. Deadlock presents. I
> rewrite  proposed code as stored procedure called "ins_rng3". I test it
> like this :

> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE



> begin



> end

If we forget the deadlock, this seems anyway to be an ineffcient way
of what you want to achieve. Now, I don't know your exact problem, but
from your narraive, your code and my imagination I came up with the
stored procedure below. You send in a series, and the length you of
the range, and you get back a start number. If there is a gap in the
ranges (because someone has deleted the range), you will get a range
in the gap, if it fits, else you will get one at the end. If the first
range was deleted, that gap is not reused though. I'm assuming that
different series and have their own ranges, but you could easily
change that.

I did some simple concurrency testing, but I got no deadlock. Probably
needs further testing though.

   CREATE TABLE ranges (series   char(2) NOT NULL,
                        startnum int NOT NULL,
                        lastnum  int NOT NULL,
                        CONSTRAINT pk_range PRIMARY KEY (series, startnum))
   go



   BEGIN TRANSACTION


                               (SELECT MAX(lastnum)
                                FROM   ranges

                               0)
   FROM   ranges a (UPDLOCK)

     AND  NOT EXISTS
          (SELECT *
           FROM   ranges b (UPDLOCK)


   --WAITFOR DELAY '00:00:10'

   INSERT ranges(series, startnum, lastnum)

   COMMIT TRANSACTION

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

1. Problem Persisting Filtered Recordset to XML DOM

Has anyone been successful persisting an ADO Recordset to an XML DOM
when the filter is set to an array of bookmarks?

We had originally tried to persist the recordset based upon a
FilterGroupenum until we found the MS Article indicating that this
cannot be done.

Supposedly, however a filter based upon an array of bookmarks works
though i have been unsuccessful in getting this to work as all records
are returned to the DOM which the working recordset contains the
correct filtered records.  the only filter i have gotten to work
correctly was explicitly setting the filter to a particular value.

TIA

Rob

2. Oracle 8 NT Client: Wierdness with App's Icons' "Start in" Property

3. Working with a remote (web) database - The problem persists 8447 Runtime Error

4. SQL Server 2000 JDBC bug with Stored Procedure Calls?

5. Problems persisting ADO 2.5 recordset using Jet.OLEDB.40

6. Lookin for ODBC Driver

7. About executing nested statements

8. Problem Persisting Filtered Recordset to XML DOM

9. ODBC/MFC problem persists...

10. IIF Problem still persists....

11. 1/1/1900 persists!! --- Date problem