[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2870
  • Last Modified:

Sybase Isolation Level 0

I am trying to run a query on Sybase using isolation level 0 in the hope that it would be faster. However, it's actually much slower than running it on isolation 1.  

It says in the Sybase manual that an identity column is needed for tables with non unique index.  I created an identity column but it doesn't seem to help.

I would appreciate any help.
0
mermood
Asked:
mermood
  • 11
  • 4
  • 2
1 Solution
 
grant300Commented:
Can you give us some specific information:
 - what does your table look like (DDL)
 - what does it contain that it does not have a unique business key
 - what is your query (DML)
 - how much data is in the table
 - what are your keys and indexes (more DDL)
 - are there any other tables involved in the query

With that we can start to figure out what your needs are.  There are some legitimate cases when you don't have a unique business key on a row (register tape on a cash register is an example) so you may be jumping through hoops you don't have to.

Also, the effect of an identity column will be felt at INSERT time when the value is assigned to the column, not at query time.

For most queries, the isolation level will have an immeasurably small affect on performance.  I suspect there are multiple thing going on here.

Regards,
Bill
0
 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
 
mermoodAuthor Commented:
DDL
=====

CREATE TABLE dbo.winTrade
(
    id           varchar(32) NOT NULL,
    source       varchar(10) NOT NULL,
    tradeXml     text        NOT NULL,
    isSynthentic tinyint     NOT NULL,
    isActive     tinyint     NOT NULL,
    lastUpdated  datetime    NOT NULL,
    CONSTRAINT winTrade_5920021091
    PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
    ON dbo.winTrade(source,isSynthentic,isActive)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade') AND name='winTrade1')
    PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go


Query
======
select  
         trade0_.id           as id,
         trade0_.source       as source,
         trade0_.tradeXml     as tradeXml,
         trade0_.isSynthentic as isSynthe4_,
         trade0_.isActive     as isActive,
         trade0_.lastUpdated  as lastUpda6_
from     winTrade trade0_
where    trade0_.source ='SALES'
         and trade0_.isSynthentic =0
         and trade0_.isActive =1
    and (not exists (select tradematch1_.tradeId1
                                 from   winTradeMatch_temp tradematch1_,
                                        winRuleContext rulecontex2_
                                 where  tradematch1_.contextId = rulecontex2_.id                                                                                    
                                             and rulecontex2_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch1_.tradeId1))
         and (not exists (select tradematch3_.tradeId2
                                 from   winTradeMatch_temp tradematch3_,
                                        winRuleContext rulecontex4_
                                 where  tradematch3_.contextId = rulecontex4_.id                                        
                                             and rulecontex4_.typ ='TRADE_MATCHING'
                                             and trade0_.id = tradematch3_.tradeId2))    

0
 
mermoodAuthor Commented:
ooops not quite sure what happened with my browser there.

There are 2 more tables involved - winTradeMatch_temp and winRuleContext .

There are about 1000 rows in winTrade.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Isolation level 0 is about being more relaxed with locking - so it is an optimisation for concurrency/throughput, not for response time. In fact you can generally be sure that anything that improves concurrency makes response time worse!

If you were running this on its own in a server where no-one else was running anything, isolation level 0 *at best* will run the same as it did without it. It will never run faster.

Isolation level 0 says to do dirty reads, which means if someone else is writing to one or more of the underlying tables and has not yet committed their transaction, rather than being blocked by that transaction (default behaviour, or isolation level 1), we will be allowed to read the uncommitted data.

Apart from anything else this means we don't know what results the query will return, since we don't know if we'll be reading the data before they write to it, after they've written to it, or maybe even halfway through whatever inserts/updates/deletes they're doing. In other words this is no longer a trusted result. Dirty reads are only for when you can tolerate a "ballpark" figure rather than exactly correct results.

So let's say that's ok, we understand we have untrusted results that might be wrong. We don't want to be blocked by any writes people are doing on the table(s)... except that the table is already row-level locked, so unless people are constantly writing to the table (and taking a long time to do it), we're probably not going to be blocked very much anyway... unless they're taking out exclusive table locks, in which case datarows locking won't help you.

If that's the case, there are almost certainly better ways to fix the problem of blocking locks - we'd ask why there are EX TBL locks and have a look at fixing those.

I note also that datarows locking is another concurrency optimisation... so in fact you made your response time worse by using row-level locking.

Lastly, isolation level 0 needs a unique index, so if a row changes while we're reading the table we know if we've seen it before or not. That business about identity columns is because the ASE engine can "cheat" and use the identity as part of a unique index.

The problem is that we *have* to use a unique index (or implicit one via the identity column) for every access to the table... whether or not that is actually a good way of finding our rows or not. So effectively you've told the query optimiser it *must* use the unique index (or fake one using the identity column) for every access to the table, regardless of what it wants to use. This is almost certainly why things got slower.

I would suggest that rather than being distracted by the shiny technology, you take a step back and look at exactly what the problem is. Why did you want to use isolation level 0? Why did you want to use datarows locking? Are you having lots of locking or deadlocking problems? If your only motivations for these were to "make it go faster" then I'm afraid you've made things worse not better.
0
 
mermoodAuthor Commented:
The problem is that I am having a number of locking and deadlocking issues.  Hence, I wanted to use isolation level 0 or dirty reads.  The deadlock occurs when one thread on winTrade table and trying to get a lock on winTradeMatchTemp and another thread is writing to winTradeMatchTemp and trying to get a lock on winTrade.

I have changed the definitions on winTradeMatchTemp and winTrade from APL to DOL and that seems to do the trick.  It is not running any faster but at least not any slower.

Any concerns about APL -> DOL?
0
 
Joe WoodhousePrincipal ConsultantCommented:
Deadlocks can only come from two places:

1) internal Sybase ASE code
2) external SQL being submitted

If you have enabled "backwards scans" (via sp_configure) then disable it again. This is the only thing you can do about #1.

Do you have any control over #2? The classic deadlock scenario is where two different parts of an app access tables in different orders.

Isolation level 0 isn't really intended to resolve locking or deadlock problems; it's for when senior management need to know an answer Real Fast Now and are willing to get a somewhat fuzzy and inaccurate answer for the sake of getting it faster. Remember the results from dirty reads might just plain be wrong - definitely not an ongoign workaround for locking problems!

Fixing the SQL is really going to give you the best results, but if that isn't an option, then sure, DOL is the way to go.

There are *many* concerns about DOL - but they are all manageable. 8-)

When you switch a table to DOL:

- all response times on that table get slightly worse
- the table takes up more space immediately
- the table is now suddenly prone to a few fragmentation problems that can't affect APL tables, so you now need to include regular defragmentation in your DBA maintenance

About the only other thing I'd add is that I see you've gone straight to "datarows" locking. Did you try datapages first? Often that's enough to fix deadlocking problems (since most deadlocks are actually on indexes rather than on data). Try switching all tables involved to datapages, and if that isn't enough, switch one at a time to datarows. I prefer datapages because some of the overhead per above is reduced on them, and you don't need as many locks. (This is a little controversial, many Sybase experts don't agree with me on it.)
0
 
grant300Commented:
I agree with Joe about a lot of things and datapages versus datarows locking is one of them.  There are certainly circumstances where you need datarows locking however, I have found them to be just a few select cases, e.g. workflow applications, queues in tables, etc.  All of this assumes the application is designed/coded reasonably well.

Bill
0
 
mermoodAuthor Commented:
You brought up a very good point - datapages.  I will try datapages and see what the performance is.

Actually, what's the difference between datarows and datapages.  I have been told datarows is more efficient?
0
 
mermoodAuthor Commented:
Or rather when do you use datarows vs datapages?
0
 
Joe WoodhousePrincipal ConsultantCommented:
Allpages - lock one page at a time. Lock any index pages you touched as part of touching the data page(s). (So if doing a SELECT through an index, lock the index pages too; a SELECT that table scans won't lock the index pages.)

DOL - data only locking, never lock index pages

Datapages - like allpages, but only the data pages. Indexes are never locks. Any access of a data page locks the entire page.

Datarows - row level locking. Never lock indexes.


DOL in general have more overhead for space, response times and ongoing maintainenance. Datarows is more expensive than datapages for two main reasons:

1) You need more locks! If you can fit 20 rows on a page, then accessing the same data now needs 20 times as many locks. They eat memory which you'd really rather spend on caches instead.

2) More overhead! ASE has to track lock dependencies, lock grants, lock waits, deadlocks, etc, at a finer level of granularity. It's like standing by a busy railway and trying to count individual people in the carriages vs just counting the trains.


I think best practice to reduce locking problems and deadlocks is:

1) Fix all SQL and transaction design issues in the code (this gains you 80% of the benefits)
2) Switch one table at a time to datapages locking. Do not switch all of them at once! (15%)
3) If all tables involved are now datapages, switch one of them at a time - starting with the smallest! - to datarows. (5%)

You will occasionally see some written recommendations to go directly to datarows and only fall back to datapages if something goes wrong. I'm really not sure where that comes from, I think there was early distrust of datapages as a "compromise" solution when DOL was introduced in ASE 11.9.2. Yet when most locking problems are on index pages rather than data pages (stands to reason - you might fit 20 rows on a data page, but 200 on an index page), then datapages is a good first choice.
0
 
mermoodAuthor Commented:
I have just switch the tables from datarows to datapages.  And I am seeing the same performance on them!  I might just use datapages for the larger table (ie. 30 000 rows) and datarows for the smaller one (ie. 3 rows).

Thanks for your help!!!
0
 
Joe WoodhousePrincipal ConsultantCommented:
I won't say there's no good reason to ever use datarows... just no good reason to go to datarows without trying datapages first. 8-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now