Setting the isolation levels can eliminate contention problems, when reports
are run on the same database as the online application.
Three flavors to choose from, for troublesome reports, queries, and updates.
1)
select cus_id
from customer_location
where cus_id< 1000000
at isolation read uncommitted
-- > Allows table to be read (ala Oracle) even when update page locks are pending.
2)
select cus_id from
customer_location noholdlock
where cus_id< 1000000
-- > Allows big queries to run without locking pages / tables.
3)
/* For updates: */
-- Session #1
begin transaction
update invoice_item
set discount_amt = 0
where invoice_id < 2000000
commit transaction
-- Session #2
set transaction isolation level 0
Queries against invoice_item in session #2 will NOT be blocked, even before the commit occurs.
Updates against invoice_item in session #2 where pages included in the Session #1 transaction WILL be blocked, before the commit occurs.
*** Mixing 1 & 2 from above is not recommended
|
|
