blocking issue 1

[Yale]

how do i resolve blocking issues? is there something that i could place in a sql script for it to avoid causing the blocking of transactions?

thanks,
YALE

 

[vongrunt]

Use NOLOCK in situations where dirty reads don't cause potential problems.

But above everything - make queries as faster as possible.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Yale]

if for example i use "nolock" in my transaction query (say query1)... then query1 will not block other transactions that may come after it (i.e. running query 1 will not lock any data)... is that correct?

thanks so much,
yale

 

[vongrunt]

If two transactions modify data, you can't avoid blocking without risking violation of ACID rules. That's why making transactions as shorter as possible is an imperative.

NOLOCK is primarily used on SELECT statements. It tells server not to place shared locks on SELECTed data so reader (process using SELECT) won't block writer (another process that attempts to modify the same data). Price for that are possible dirty reads - writer attempts to modify data, reader SELECTs modified (uncommitted!) data, writer rolls back... and as a result, reader got data that virtually never existed. Such situations are sometimes harmless (aka: never gonna happen) and sometimes very unpleasant.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)