database - 数据库查询失败的原因是什么？

Question

给定一个数据库，查询在 99% 的时间内返回正确的输出，但是在 1% 的时间内返回错误的输出。可能的原因是什么？

Answer 1

查询返回“错误”或“非预期”结果集的原因有多种:

1. “错误的”数据被添加到数据库/表中。
2. 查询读取未提交的记录或幻像记录(参见数据库隔离级别)
3. 您的数据可能尚未编制索引(如果是全文索引)或全文服务正在重建索引
4. 查询中没有ORDER BY子句，系统负载过高。在这种情况下，我们可能会观察到“旋转木马扫描”:

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. 在分布式数据库的情况下，可以在数据库的不同副本上执行查询(因为“数据库负载平衡器”)。因此，在复制过程中，某些数据库副本可能在某些时候具有不同的数据。

如果出现其他想法 - 我会更新我的帖子。