Loading ...
Sorry, an error occurred while loading the content.

Re: DeadLock issue in parallel process on sql 2008

Expand Messages
  • sree
    Hi Vijay, Shuffled the order of the tables based on the elapsed time calculation and changed the stored procedures, implemented the same on jobs. It s worked
    Message 1 of 11 , May 20, 2009
    • 0 Attachment
      Hi Vijay,

      Shuffled the order of the tables based on the elapsed time calculation and changed the stored procedures, implemented the same on jobs. It's worked fine now.

      As you suggested, Index part I have to try in the next fund migration and we have not implemented any constraint concept in the migration process.

      Thank you
      Vemuri

      --- In sqlcon@yahoogroups.com, "vijaya_krishna_birju" <vijaya_krishna_birju@...> wrote:
      >
      > Hi Vemuri,
      > Haven't heard anything on this!! are you still facing the issue.
      > Thanks -- Vijaya Kadiyala
      > www.DotNetVJ.com
      >
      > --- In sqlcon@yahoogroups.com, "vijaya_krishna_birju" <vijaya_krishna_birju@> wrote:
      > >
      > > Hi
      > > Can you create an index on this column? I am sure this will resolve the problem. Though i am not expert in SQL Server but when i had same issue in Oracle, after placing the indexes it resolved the issue. Also do you have foreign key relationship between these tables?
      > > Thanks -- Vijaya Kadiyala
      > > www.DotNetVJ.com
      > >
      > > --- In sqlcon@yahoogroups.com, "sree" <vemuri_sree@> wrote:
      > > >
      > > > Hi,
      > > > No Index available on these tables, becasue of data migration process.
      > > >
      > > > --- In sqlcon@yahoogroups.com, "vijaya_krishna_birju" <vijaya_krishna_birju@> wrote:
      > > > >
      > > > > So i believe when you are reading this table you are getting into the DeadLock.!!! Do you have Indexes on these columns?
      > > > > Thanks -- Vijaya Kadiyala
      > > > > www.DotNetVJ.com
      > > > >
      > > > > --- In sqlcon@yahoogroups.com, "sree" <vemuri_sree@> wrote:
      > > > > >
      > > > > > Hi Vijay,
      > > > > >
      > > > > > Yes, I am reading other tables also and running on SQL server 2008.
      > > > > > Thanks in Advance.
      > > > > >
      > > > > > Regards,
      > > > > > Vemuri
      > > > > >
      > > > > > --- In sqlcon@yahoogroups.com, "vijaya_krishna_birju" <vijaya_krishna_birju@> wrote:
      > > > > > >
      > > > > > > Hi Vemuri,
      > > > > > > While performing this insert operation, are you reading any other table? Also which version of the SQL Server you are running?
      > > > > > > Thanks -- Vj
      > > > > > > www.DotNetVJ.com
      > > > > > >
      > > > > > > --- In sqlcon@yahoogroups.com, "sree" <vemuri_sree@> wrote:
      > > > > > > >
      > > > > > > > I have a problem with Deadlocks in parallel process and getting the below error.
      > > > > > > >
      > > > > > > > "Msg 245, Sev 16, State 1, Line 475: Conversion failed when converting the nvarchar value 'Transaction (Process ID 98) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.' to data type int. [SQLSTATE 22018]"
      > > > > > > >
      > > > > > > > Details: Running 2 jobs on same table, which will call the same procedure and it will run based on the batch wise. Ex. Job 1 – 1 to 100000 and Job2 -- 100001 to 200000 etc..
      > > > > > > >
      > > > > > > > When it accesses the same table, it's giving the above error. I have tried with profiler and checked the same, in that I found lock timeout and lock cancel … but if I run the jobs in 30 min gap, its running fine.
      > > > > > > >
      > > > > > > > I have to run the jobs in parallel and tried with set lock_timeout , DEADLOCK_PRIORITY .Could you please help in this , how best I can go with parallel process in data transformation.
      > > > > > > >
      > > > > > > > Thanks in advance
      > > > > > > > Vemuri
      > > > > > > >
      > > > > > >
      > > > > >
      > > > >
      > > >
      > >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.