Transaction deadlock

[Ivan]

Hi All,

Does anyone know how to prevent dead lock when
selecting records within Transaction??

Let me describe abit clear.
I created & open a transaction and before i commit/ rollback the transaction
i have to select few records within the same table
but the problem is once i execute a select sql statement for serveral times
(usually more than 2 times)... the select sql will be hold and the whole
application will be hung...
I guess the reason is because the transaction haven't finished
and as a result the select sql should be on queue

I have tried various solutions like changing the transaction level ... but
none of them work as needed

so.. please tell me if anyone know the solutions... thanks to all

Ivan

 

[Miha Markic [MVP C#]]

Hi Ivan,

Are you executing select within the same transaction or are you using
another connection/transaction?
Which database are you using?

 

[Mary Chipman]

One thing you might try is to execute the SELECT with the nolock hint.
That should give you the uncommitted dirty read of the rows, if that
is what you are looking for.

--Mary

 

[Ivan]

Hi Mary,

Thanks for your reply...
actually, what do u mean the nolock hint... I don't quite understand...

btw... i found a solution for my situation...
which is, if i don't execute 2 same (exactly the same) SELECT statement..
the deadlock won't happen... however, i still wonder how to fix it...
coz i 'm sure i'll face the similar situation again later....

Ivan

 

[Mary Chipman]

The "Locking Hints" topic in SQL Books Online explains the different
kinds of hints. Basically your Select statement looks like this:

SELECT <field list> FROM <tablename> WITH (NOLOCK)

But what you get is what's called a dirty read, or data that has not
been committed. So if you issue it in the middle of a transaction
against rows that have been updated but not committed yet, you'll see
data that may or may not actually end up in the database. OTOH, this
query won't honor any xlocks held by other transactions, either.

--Mary