"pdsluk" <pds***@discussions.microsoft.com> wrote in message
news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> Hi
>
> I am new to transactions and I am not sure if this is the correct method,
> any help would be appreciated.
>
> My transaction involves reading data from serveral tables and making two
new
> inserts into other tables. I want to ensure what is read and written stays
as
> that until the transaction is complete.
>
> My code for the selects and inserts is after
>
> Dim trans As SqlTransaction
> Dim conn As SqlConnection
> Dim comm As SqlCommand
> conn = New SqlConnection(myConnectionData)
> conn.Open()
> comm = conn.CreateCommand()
> trans = conn.BeginTransaction(IsolationLevel.Serializable)
> comm.Connection = conn
> comm.Transaction = trans
>
>
> and ends in
>
> trans.Commit()
>
> I am under the impression by using IsolationLevel.Serializable no other
> connection can read or write the data in the tables used in my
transaction.
>
> All is working well most of the time and I can induce rollbacks ok.
>
> Sometimes I am getting
>
> Transaction (Process ID 52) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction
>
> Why would I get a deadlock ? I thought no other connection could create
any
> locks until my transaction has completed ?
>
> Thanks
>
>

"Sahil Malik [MVP]" wrote:

> Serializable is effectively the highest isolation level which means greatest
> data sanctity at the cost of lowest concurrency i.e. pessimistic locking.
> You should change it to ReadCommitted, and the deadlocks will go away. Make
> sure however that you follow optimistic concurrency checks to ensure that
> your data isn't corrupted.
>
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
>
>
>
>
> "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> > Hi
> >
> > I am new to transactions and I am not sure if this is the correct method,
> > any help would be appreciated.
> >
> > My transaction involves reading data from serveral tables and making two
> new
> > inserts into other tables. I want to ensure what is read and written stays
> as
> > that until the transaction is complete.
> >
> > My code for the selects and inserts is after
> >
> > Dim trans As SqlTransaction
> > Dim conn As SqlConnection
> > Dim comm As SqlCommand
> > conn = New SqlConnection(myConnectionData)
> > conn.Open()
> > comm = conn.CreateCommand()
> > trans = conn.BeginTransaction(IsolationLevel.Serializable)
> > comm.Connection = conn
> > comm.Transaction = trans
> >
> >
> > and ends in
> >
> > trans.Commit()
> >
> > I am under the impression by using IsolationLevel.Serializable no other
> > connection can read or write the data in the tables used in my
> transaction.
> >
> > All is working well most of the time and I can induce rollbacks ok.
> >
> > Sometimes I am getting
> >
> > Transaction (Process ID 52) was deadlocked on lock resources with another
> > process and has been chosen as the deadlock victim. Rerun the transaction
> >
> > Why would I get a deadlock ? I thought no other connection could create
> any
> > locks until my transaction has completed ?
> >
> > Thanks
> >
> >
>
>
>

"pdsluk" <pds***@discussions.microsoft.com> wrote in message
news:E495D9BD-40AC-4F51-9B7F-43B7B43BC4D3@microsoft.com...
> Hi
>
> I am trying to protect a select query from changing whilst the the
> transaction is being written out, if I choose another isolation level will
> that mean another process can read the same data and act upon it, if so
how
> can I protect against this. I want only one process at a time to complete
the
> transaction based upon the read data.
>
> "Sahil Malik [MVP]" wrote:
>
> > Serializable is effectively the highest isolation level which means
greatest
> > data sanctity at the cost of lowest concurrency i.e. pessimistic
locking.
> > You should change it to ReadCommitted, and the deadlocks will go away.
Make
> > sure however that you follow optimistic concurrency checks to ensure
that
> > your data isn't corrupted.
> >
> > - Sahil Malik [MVP]
> > http://codebetter.com/blogs/sahil.malik/
> >
> >
> >
> >
> >
> >
> > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> > > Hi
> > >
> > > I am new to transactions and I am not sure if this is the correct
method,
> > > any help would be appreciated.
> > >
> > > My transaction involves reading data from serveral tables and making
two
> > new
> > > inserts into other tables. I want to ensure what is read and written
stays
> > as
> > > that until the transaction is complete.
> > >
> > > My code for the selects and inserts is after
> > >
> > > Dim trans As SqlTransaction
> > > Dim conn As SqlConnection
> > > Dim comm As SqlCommand
> > > conn = New SqlConnection(myConnectionData)
> > > conn.Open()
> > > comm = conn.CreateCommand()
> > > trans = conn.BeginTransaction(IsolationLevel.Serializable)
> > > comm.Connection = conn
> > > comm.Transaction = trans
> > >
> > >
> > > and ends in
> > >
> > > trans.Commit()
> > >
> > > I am under the impression by using IsolationLevel.Serializable no
other
> > > connection can read or write the data in the tables used in my
> > transaction.
> > >
> > > All is working well most of the time and I can induce rollbacks ok.
> > >
> > > Sometimes I am getting
> > >
> > > Transaction (Process ID 52) was deadlocked on lock resources with
another
> > > process and has been chosen as the deadlock victim. Rerun the
transaction
> > >
> > > Why would I get a deadlock ? I thought no other connection could
create
> > any
> > > locks until my transaction has completed ?
> > >
> > > Thanks
> > >
> > >
> >
> >
> >

"Sahil Malik [MVP]" wrote:

> Under optimistic concurrency, you can wrap the operation of
>
> "Check if anyone has changed my data, and write only if my data hasn't
> changed since I read it" <-- that much into one transaction using
> ReadCommitted isolation level.
>
> The other details are specific to db .. are you using Sql Server?
>
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
>
> "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> news:E495D9BD-40AC-4F51-9B7F-43B7B43BC4D3@microsoft.com...
> > Hi
> >
> > I am trying to protect a select query from changing whilst the the
> > transaction is being written out, if I choose another isolation level will
> > that mean another process can read the same data and act upon it, if so
> how
> > can I protect against this. I want only one process at a time to complete
> the
> > transaction based upon the read data.
> >
> > "Sahil Malik [MVP]" wrote:
> >
> > > Serializable is effectively the highest isolation level which means
> greatest
> > > data sanctity at the cost of lowest concurrency i.e. pessimistic
> locking.
> > > You should change it to ReadCommitted, and the deadlocks will go away.
> Make
> > > sure however that you follow optimistic concurrency checks to ensure
> that
> > > your data isn't corrupted.
> > >
> > > - Sahil Malik [MVP]
> > > http://codebetter.com/blogs/sahil.malik/
> > >
> > >
> > >
> > >
> > >
> > >
> > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > > news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> > > > Hi
> > > >
> > > > I am new to transactions and I am not sure if this is the correct
> method,
> > > > any help would be appreciated.
> > > >
> > > > My transaction involves reading data from serveral tables and making
> two
> > > new
> > > > inserts into other tables. I want to ensure what is read and written
> stays
> > > as
> > > > that until the transaction is complete.
> > > >
> > > > My code for the selects and inserts is after
> > > >
> > > > Dim trans As SqlTransaction
> > > > Dim conn As SqlConnection
> > > > Dim comm As SqlCommand
> > > > conn = New SqlConnection(myConnectionData)
> > > > conn.Open()
> > > > comm = conn.CreateCommand()
> > > > trans = conn.BeginTransaction(IsolationLevel.Serializable)
> > > > comm.Connection = conn
> > > > comm.Transaction = trans
> > > >
> > > >
> > > > and ends in
> > > >
> > > > trans.Commit()
> > > >
> > > > I am under the impression by using IsolationLevel.Serializable no
> other
> > > > connection can read or write the data in the tables used in my
> > > transaction.
> > > >
> > > > All is working well most of the time and I can induce rollbacks ok.
> > > >
> > > > Sometimes I am getting
> > > >
> > > > Transaction (Process ID 52) was deadlocked on lock resources with
> another
> > > > process and has been chosen as the deadlock victim. Rerun the
> transaction
> > > >
> > > > Why would I get a deadlock ? I thought no other connection could
> create
> > > any
> > > > locks until my transaction has completed ?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> > >
>
>
>

"pdsluk" <pds***@discussions.microsoft.com> wrote in message
news:D38245AB-B044-4130-A1E2-00041BF1EB35@microsoft.com...
> Yes I am using SQL Server
>
> I have many instances all doing the same process and I only want the first
> that detects the condition to be allowed to complete the the transaction.
I
> don't understand if the first process has put a lock on all tables
concerned
> that a deadlock can occur. Can you explain a bit more.
>
> Many thanks for you assistance
>
> "Sahil Malik [MVP]" wrote:
>
> > Under optimistic concurrency, you can wrap the operation of
> >
> > "Check if anyone has changed my data, and write only if my data hasn't
> > changed since I read it" <-- that much into one transaction using
> > ReadCommitted isolation level.
> >
> > The other details are specific to db .. are you using Sql Server?
> >
> > - Sahil Malik [MVP]
> > http://codebetter.com/blogs/sahil.malik/
> >
> >
> >
> > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > news:E495D9BD-40AC-4F51-9B7F-43B7B43BC4D3@microsoft.com...
> > > Hi
> > >
> > > I am trying to protect a select query from changing whilst the the
> > > transaction is being written out, if I choose another isolation level
will
> > > that mean another process can read the same data and act upon it, if
so
> > how
> > > can I protect against this. I want only one process at a time to
complete
> > the
> > > transaction based upon the read data.
> > >
> > > "Sahil Malik [MVP]" wrote:
> > >
> > > > Serializable is effectively the highest isolation level which means
> > greatest
> > > > data sanctity at the cost of lowest concurrency i.e. pessimistic
> > locking.
> > > > You should change it to ReadCommitted, and the deadlocks will go
away.
> > Make
> > > > sure however that you follow optimistic concurrency checks to ensure
> > that
> > > > your data isn't corrupted.
> > > >
> > > > - Sahil Malik [MVP]
> > > > http://codebetter.com/blogs/sahil.malik/
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > > > news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> > > > > Hi
> > > > >
> > > > > I am new to transactions and I am not sure if this is the correct
> > method,
> > > > > any help would be appreciated.
> > > > >
> > > > > My transaction involves reading data from serveral tables and
making
> > two
> > > > new
> > > > > inserts into other tables. I want to ensure what is read and
written
> > stays
> > > > as
> > > > > that until the transaction is complete.
> > > > >
> > > > > My code for the selects and inserts is after
> > > > >
> > > > > Dim trans As SqlTransaction
> > > > > Dim conn As SqlConnection
> > > > > Dim comm As SqlCommand
> > > > > conn = New SqlConnection(myConnectionData)
> > > > > conn.Open()
> > > > > comm = conn.CreateCommand()
> > > > > trans = conn.BeginTransaction(IsolationLevel.Serializable)
> > > > > comm.Connection = conn
> > > > > comm.Transaction = trans
> > > > >
> > > > >
> > > > > and ends in
> > > > >
> > > > > trans.Commit()
> > > > >
> > > > > I am under the impression by using IsolationLevel.Serializable no
> > other
> > > > > connection can read or write the data in the tables used in my
> > > > transaction.
> > > > >
> > > > > All is working well most of the time and I can induce rollbacks
ok.
> > > > >
> > > > > Sometimes I am getting
> > > > >
> > > > > Transaction (Process ID 52) was deadlocked on lock resources with
> > another
> > > > > process and has been chosen as the deadlock victim. Rerun the
> > transaction
> > > > >
> > > > > Why would I get a deadlock ? I thought no other connection could
> > create
> > > > any
> > > > > locks until my transaction has completed ?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

"Sahil Malik [MVP]" wrote:

> Okay if you are using SQL Server, then simply use the rowversion or
> timestamp column to enforce concurrency checks. This problem will go away
> then.
>
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
>
>
>
> "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> news:D38245AB-B044-4130-A1E2-00041BF1EB35@microsoft.com...
> > Yes I am using SQL Server
> >
> > I have many instances all doing the same process and I only want the first
> > that detects the condition to be allowed to complete the the transaction.
> I
> > don't understand if the first process has put a lock on all tables
> concerned
> > that a deadlock can occur. Can you explain a bit more.
> >
> > Many thanks for you assistance
> >
> > "Sahil Malik [MVP]" wrote:
> >
> > > Under optimistic concurrency, you can wrap the operation of
> > >
> > > "Check if anyone has changed my data, and write only if my data hasn't
> > > changed since I read it" <-- that much into one transaction using
> > > ReadCommitted isolation level.
> > >
> > > The other details are specific to db .. are you using Sql Server?
> > >
> > > - Sahil Malik [MVP]
> > > http://codebetter.com/blogs/sahil.malik/
> > >
> > >
> > >
> > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > > news:E495D9BD-40AC-4F51-9B7F-43B7B43BC4D3@microsoft.com...
> > > > Hi
> > > >
> > > > I am trying to protect a select query from changing whilst the the
> > > > transaction is being written out, if I choose another isolation level
> will
> > > > that mean another process can read the same data and act upon it, if
> so
> > > how
> > > > can I protect against this. I want only one process at a time to
> complete
> > > the
> > > > transaction based upon the read data.
> > > >
> > > > "Sahil Malik [MVP]" wrote:
> > > >
> > > > > Serializable is effectively the highest isolation level which means
> > > greatest
> > > > > data sanctity at the cost of lowest concurrency i.e. pessimistic
> > > locking.
> > > > > You should change it to ReadCommitted, and the deadlocks will go
> away.
> > > Make
> > > > > sure however that you follow optimistic concurrency checks to ensure
> > > that
> > > > > your data isn't corrupted.
> > > > >
> > > > > - Sahil Malik [MVP]
> > > > > http://codebetter.com/blogs/sahil.malik/
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
> > > > > news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
> > > > > > Hi
> > > > > >
> > > > > > I am new to transactions and I am not sure if this is the correct
> > > method,
> > > > > > any help would be appreciated.
> > > > > >
> > > > > > My transaction involves reading data from serveral tables and
> making
> > > two
> > > > > new
> > > > > > inserts into other tables. I want to ensure what is read and
> written
> > > stays
> > > > > as
> > > > > > that until the transaction is complete.
> > > > > >
> > > > > > My code for the selects and inserts is after
> > > > > >
> > > > > > Dim trans As SqlTransaction
> > > > > > Dim conn As SqlConnection
> > > > > > Dim comm As SqlCommand
> > > > > > conn = New SqlConnection(myConnectionData)
> > > > > > conn.Open()
> > > > > > comm = conn.CreateCommand()
> > > > > > trans = conn.BeginTransaction(IsolationLevel.Serializable)
> > > > > > comm.Connection = conn
> > > > > > comm.Transaction = trans
> > > > > >
> > > > > >
> > > > > > and ends in
> > > > > >
> > > > > > trans.Commit()
> > > > > >
> > > > > > I am under the impression by using IsolationLevel.Serializable no
> > > other
> > > > > > connection can read or write the data in the tables used in my
> > > > > transaction.
> > > > > >
> > > > > > All is working well most of the time and I can induce rollbacks
> ok.
> > > > > >
> > > > > > Sometimes I am getting
> > > > > >
> > > > > > Transaction (Process ID 52) was deadlocked on lock resources with
> > > another
> > > > > > process and has been chosen as the deadlock victim. Rerun the
> > > transaction
> > > > > >
> > > > > > Why would I get a deadlock ? I thought no other connection could
> > > create
> > > > > any
> > > > > > locks until my transaction has completed ?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

"pdsluk" <pds***@discussions.microsoft.com> wrote in message
news:1665ACAF-4A2A-4B4B-BEDF-E6D1F7A85987@microsoft.com...
> OK
>
> Thank you for yor help, I wil have to try and incorparate your suggestions
> into the application.
>
> If I am to use other methods to enforce concurrency checks then does the
> need to wrap everything in a transaction diminsh ?
>
> "Sahil Malik [MVP]" wrote:
>
>> Okay if you are using SQL Server, then simply use the rowversion or
>> timestamp column to enforce concurrency checks. This problem will go away
>> then.
>>
>> - Sahil Malik [MVP]
>> http://codebetter.com/blogs/sahil.malik/
>>
>>
>>
>>
>>
>> "pdsluk" <pds***@discussions.microsoft.com> wrote in message
>> news:D38245AB-B044-4130-A1E2-00041BF1EB35@microsoft.com...
>> > Yes I am using SQL Server
>> >
>> > I have many instances all doing the same process and I only want the
>> > first
>> > that detects the condition to be allowed to complete the the
>> > transaction.
>> I
>> > don't understand if the first process has put a lock on all tables
>> concerned
>> > that a deadlock can occur. Can you explain a bit more.
>> >
>> > Many thanks for you assistance
>> >
>> > "Sahil Malik [MVP]" wrote:
>> >
>> > > Under optimistic concurrency, you can wrap the operation of
>> > >
>> > > "Check if anyone has changed my data, and write only if my data
>> > > hasn't
>> > > changed since I read it" <-- that much into one transaction using
>> > > ReadCommitted isolation level.
>> > >
>> > > The other details are specific to db .. are you using Sql Server?
>> > >
>> > > - Sahil Malik [MVP]
>> > > http://codebetter.com/blogs/sahil.malik/
>> > >
>> > >
>> > >
>> > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
>> > > news:E495D9BD-40AC-4F51-9B7F-43B7B43BC4D3@microsoft.com...
>> > > > Hi
>> > > >
>> > > > I am trying to protect a select query from changing whilst the the
>> > > > transaction is being written out, if I choose another isolation
>> > > > level
>> will
>> > > > that mean another process can read the same data and act upon it,
>> > > > if
>> so
>> > > how
>> > > > can I protect against this. I want only one process at a time to
>> complete
>> > > the
>> > > > transaction based upon the read data.
>> > > >
>> > > > "Sahil Malik [MVP]" wrote:
>> > > >
>> > > > > Serializable is effectively the highest isolation level which
>> > > > > means
>> > > greatest
>> > > > > data sanctity at the cost of lowest concurrency i.e. pessimistic
>> > > locking.
>> > > > > You should change it to ReadCommitted, and the deadlocks will go
>> away.
>> > > Make
>> > > > > sure however that you follow optimistic concurrency checks to
>> > > > > ensure
>> > > that
>> > > > > your data isn't corrupted.
>> > > > >
>> > > > > - Sahil Malik [MVP]
>> > > > > http://codebetter.com/blogs/sahil.malik/
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > > "pdsluk" <pds***@discussions.microsoft.com> wrote in message
>> > > > > news:C7B76ECA-B5A6-4E8B-BE81-C974A8D61586@microsoft.com...
>> > > > > > Hi
>> > > > > >
>> > > > > > I am new to transactions and I am not sure if this is the
>> > > > > > correct
>> > > method,
>> > > > > > any help would be appreciated.
>> > > > > >
>> > > > > > My transaction involves reading data from serveral tables and
>> making
>> > > two
>> > > > > new
>> > > > > > inserts into other tables. I want to ensure what is read and
>> written
>> > > stays
>> > > > > as
>> > > > > > that until the transaction is complete.
>> > > > > >
>> > > > > > My code for the selects and inserts is after
>> > > > > >
>> > > > > > Dim trans As SqlTransaction
>> > > > > > Dim conn As SqlConnection
>> > > > > > Dim comm As SqlCommand
>> > > > > > conn = New SqlConnection(myConnectionData)
>> > > > > > conn.Open()
>> > > > > > comm = conn.CreateCommand()
>> > > > > > trans = conn.BeginTransaction(IsolationLevel.Serializable)
>> > > > > > comm.Connection = conn
>> > > > > > comm.Transaction = trans
>> > > > > >
>> > > > > >
>> > > > > > and ends in
>> > > > > >
>> > > > > > trans.Commit()
>> > > > > >
>> > > > > > I am under the impression by using IsolationLevel.Serializable
>> > > > > > no
>> > > other
>> > > > > > connection can read or write the data in the tables used in my
>> > > > > transaction.
>> > > > > >
>> > > > > > All is working well most of the time and I can induce rollbacks
>> ok.
>> > > > > >
>> > > > > > Sometimes I am getting
>> > > > > >
>> > > > > > Transaction (Process ID 52) was deadlocked on lock resources
>> > > > > > with
>> > > another
>> > > > > > process and has been chosen as the deadlock victim. Rerun the
>> > > transaction
>> > > > > >
>> > > > > > Why would I get a deadlock ? I thought no other connection
>> > > > > > could
>> > > create
>> > > > > any
>> > > > > > locks until my transaction has completed ?
>> > > > > >
>> > > > > > Thanks
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>