Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    13

    Unanswered: Transaction Atomicity in Sql Server 2005

    Hi I am using sql server 2005 with VB.Net and sqlconnection connection object.

    Basically My question is about Atomicity issue. I have several staged where I need to Insert a record in a Table A in a loop. Simultaneously Update an Row of Table A. (for Example normally if a fresh row is inserted so the old row must be marked a inactive or something like this). This is not enough I sometime have four tables where I insert the rows frequently and update others rows.

    when I insert the row there is no problem, but when I update an old row it makes problem and throughs exception and my transaction not gets committed. The problem is very clear that the entire table A have got locked for insertion so there is no chance for update. Is it possible that I update the without leaving atomicity or If I can lock only the new inserted lock of my said transaction thread.

    Please let me have the solution asap.

    Thanks in advance.

    Jay Khatri

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Row locks are not only possible in SQL 2005, they are rather common. Do you have indexes on the tables in question? Have you identified the blocking process, and seen if its query can be optimized?

  3. #3
    Join Date
    Oct 2010
    Posts
    13
    Yes I have indexes on that table. Even I maintain index on every table with identity. No Don't identified any blocking process but I started the transaction with connection.BeginTransaction where i started dml. I have one connection for insert and update and one global sqlcommand.

    The thing is I need atomicity as I am doing marksheet calculation. So if any exception occur in the middle. I want everything to get rolledback.

    Thank you very much MCrowly and hope to get the solution.

    Best Regards

    Jay Khatri

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Does the update query use the index? Having the index is no guarantee it will be used.

  5. #5
    Join Date
    Oct 2010
    Posts
    13
    so far these are the two queries being used in a loop, first query insert a new record and query updates old record, the primary key field is st_sess_cls_id which here is not used:

    str = "insert into session_student_class (reg_id,class_id,session_id,sec_id, pro_status,ses_cls_id,stream_id) values (" & Regid & "," & NextCls & "," & Cur_Session & "," & SecID & ",2," & NSesNClsId & "," & streamid & ")"

    clscmn.inserts(str)

    str = "update session_student_class set promst=1 where reg_id=" & Regid & " and ses_cls_id=" & SesClsID

    cmnmod.Updates(str)

    I like to have this all to be done either in a single shot or not even any of it. I have several queries also which I am converting from MS-Access to sql server as MS-Access don't provide transaction states and bad in concurrency too.

    I wonder if it possible to mention the row level lock so that only new inserted (not committed records get locked not the old ones) and I think this must be possible in any way as everybody needs atomicity.

    Thank you in advance.

    Best Regards

    Jay Khatri

Tags for this Thread

Posting Permissions

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