Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: Dblocks with temporary table

    Hi,

    I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.

    But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.

    Does each thread create its own copy of temp table ?
    Is temdb locked in this process ?
    Do I need to drop the tamp table at the end ? ( I am not dropping now)
    What are the other alternatives ?

    I am on SQL Server 7, windows 2000/NT.

    Thanx..
    -Bheemsen

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    The temp table is first created and rows are inserted...

    Which approach do you use?

    1. select into
    2. create table + insert

    Select into locks system tables in MSSQL7.

  3. #3
    Join Date
    May 2002
    Posts
    62
    Thanx ispaleny. I am using the 2 option.
    i.e. create table, then insert, then select.

    -Bheemsen

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I use MSSQL2k.

    #tables are created unique for each thread
    #tables created in scope of SP exist only in scope of SP

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Question

    RE:
    Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen
    Question I
    Since it isn't a simple select into locking issue, what are the server's wait states like when the issue presents itself? Also, what kinds of locks are being issued and in what proportion, and is the Java app possibly spawning multiple connections (rather than reusing when possible) and / or not closing out connections when done with them?

    wait states data gathering example:

    Select
    Spid,
    Waittime,
    Lastwaittype,
    Waitresource
    From
    Master..Sysprocesses
    Where
    Waittime > 300

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •