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.
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.
str = "update session_student_class set promst=1 where reg_id=" & Regid & " and ses_cls_id=" & SesClsID
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.