Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Transact-SQL » Coding » Locking

Isolation Levels

     




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











Sybase : Related Topics

Sybase : Transact-SQL : Transact SQL: numeric functions
Sybase : Transact-SQL : Transact SQL: string functions
Sybase : Transact-SQL : Transact SQL: date/time functions
Sybase : Transact-SQL : Transact SQL: misc functions
Sybase : Transact-SQL : Transact SQL: Conditionals
Sybase : Transact-SQL : Transact SQL: looping constructs
Sybase : Transact-SQL : Transact SQL: Cursors
Sybase : Transact-SQL : Transact SQL: Complex Updates
Sybase : Transact-SQL : Transact SQL: Finding duplicate rows in a table
Sybase : Transact-SQL : Using Temporary Tables
Sybase : Transact-SQL : Inner/Outer Joins
Sybase : Transact-SQL : Reporting: SQL Performance and Tuning
Sybase : Transact-SQL : Case Statement
Sybase : Transact-SQL : Date processing: stripping time from datetime
Sybase : Transact-SQL : Safely delete a large number of rows without blowing the t-log
Sybase : Transact-SQL : Transact SQL: date/time formats
Sybase : Transact-SQL : Creating a stored procedure
Sybase : Transact-SQL : Custom query plans
Sybase : Transact-SQL : Rowcount setting - limiting result sets
Sybase : Transact-SQL : Ranking Data - with Duplicates
Sybase : Transact-SQL : Forcing an index in a query
Sybase : Transact-SQL : Median Calculation, with pure SQL
Sybase : Transact-SQL : Lead and Lag with pure SQL

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.