CODEWITHSUNDEEP
database
user1159517
user1159517

Reputation: 6320

What are reasons for query failures in databases?

Given a database, a query returns correct output 99% of the time, however 1% of the time, it returns wrong output. What could be the possible reasons?

Upvotes: 3

Views: 1197

Answers (3)

Pavel Podlipensky
Pavel Podlipensky

Reputation: 8269

There are several reasons why the query return "wrong" or "not expected" result set:

  1. "Wrong" data were added to the database/table.
  2. Query reads uncommitted records or phantom records (see database levels of isolation)
  3. Your data may not be indexed yet (in case of fulltext index) or fulltext service is rebuilding the index
  4. There is no ORDER BY clause in the query and the system is high-loaded. In this case we may observe "merry-go-round scanning":

For example, assume that you have a table with 500,000 pages. UserA executes a Transact-SQL statement that requires a scan (and retrieve some records) of the table. When that scan has processed 100,000 pages, UserB executes another Transact-SQL statement that scans the same table. The Database Engine schedules one set of read requests for pages after 100,001, and passes the rows from each page back to both scans. When the scan reaches the 200,000th page, UserC executes another Transact-SQL statement that scans the same table. Starting with page 200,001, the Database Engine passes the rows from each page it reads back to all three scans. After it reads the 500,000th row, the scan for UserA is complete, and the scans for UserB and UserC wrap back and start to read the pages starting with page 1. When the Database Engine gets to page 100,000, the scan for UserB is completed. The scan for UserC then keeps going alone until it reads page 200,000. At this point, all the scans have been completed. More about this optimization here: merry-go-round scanning

  1. In case of distributed databases, query could be performed on different copies of the database (because of "database load balancer"). So, during replication procedure some database copies may have different data at some point.

If some other ideas comes up - I'll update my post.

Upvotes: 2

summercostanza
summercostanza

Reputation: 295

i think maybe they did not put check for special charactor and 1 % of time user may insert special character.

Upvotes: 1

scibuff
scibuff

Reputation: 13755

cache, synchronization/duplication, lack of resources, etc

Upvotes: 0

Related Questions