Long running DB query with concurrent write at the same time [closed]

Question

We have a big table with a lot of data and a select query which takes 3 seconds to run.

However we are a highly concurrent environment and each second we get 100 new records in our database.

So let's say we have this query, and before that query starts, we have 1000 items which satisfy those query. The query takes 3 seconds, and each second there are 50 new items that match those query added to the DB.

My question is: What result will this query return to me (is it 1000, 1150 or somewhere in between), and how does this vary depending on the DB engine (SQL, NoSQL).

It's not a question about the exact number, more - why it would actually be that number.

Answer 1

You're essentially asking about how locks are performed and when. This will be covered in the documentation for each database implementation.

As an example for PostgreSQL the required reading is:

http://www.postgresql.org/docs/9.4/static/transaction-iso.html

Specifically you'll see it's up to the programmer to set isolation levels. That said there are defaults like:

Read Committed is the default isolation level in PostgreSQL.

And then it goes onto explain it. The table explains what can occur at each isolation level.

You can also use explicit locking in most RDMS:

http://www.postgresql.org/docs/9.4/static/explicit-locking.html

This explains the default also at least for select:

ACCESS SHARE - The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

If you want to ensure you're not reading anything added after the query starts you can use "EXCLUSIVE". From the table you can see what this means.

This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

Which can be useful if your problem requires that.

Regarding NoSQL there's so many of them. I'm not sure where to start, but glancing at the wikipedia article on NoSQL it indicates some don't even offer isolation. (Or ACID).

Answer 2

In RDBMS parlance, you're really talking about Isolation Levels. How these vary depends on the RDBMS in question (and also the version).

Perhaps the simplest scenario is Read Committed vs Read Uncommitted. If you wish to read only clean data i.e. that which has been committed to the database then the result may differ from uncommitted data which may have been valid at the time but which has since been rolled back. A subtlety which is lost on many a new developer lacking database nous is that while clean reads return "correct" data, dirty reads can be faster since they don't have to wait for transactions to finish to complete the picture of the data.

Whether spurious data actually matters varies from case to case. Clearly, banking and financial applications absolutely require accurate data whilst say, pulling a gender split from a large data warehouse for a management report would perhaps require a lower level of accuracy.

For certain tasks, you may wish there to be a consistent view of the data for the duration of your operation in which case, a snapshot of the data is taken and the query works against that until the operation is done no matter what records are added to the later data vintage.

In addition to the query time, there is also the consideration of how quickly new records are written. If there are a high number of indexes on the table then new records will be added at a lower rate than if there were a low number of indexes on the table.

Answer 3

i have ms-sql server experience with reporting queries on a (realtime replica) database receiving hundreds of rows / second.

The short answer is always going to be "it depends"; the answer can be anything between 1000 and 1150 (or even 990 to 1160 if you run with "Read Uncommitted" as mentioned by Robbie Dee)

in sql server a table scan,by default, locks pages as it reads, ensuring that re-use of the same row doesn't give a silly-inconsistent answer. Writes which happen after you start the query, but involving a row/data page you have not yet read will be visable to you.

Adjusting the isolation level can change this in 2 ways:

1) with 'snapshot' isolation, you will not see writes which are requested after you start the query. (what happens to the write depends on if you have a row versioning database, such as oracle, or sql-server with snapshot isolation enabled)

2) when you use a more permissive "allow dirty reads" Read Uncommited (or a table 'with (nolock)' ) then what happens is undefined. in particuar 3 extra scenarios can occur when reading a full page that is about to be split:

a) you are at the end of a page, and the new page is ahead of the table scan; you will read all the rows copied to the new page twice. (potential primary key violation in the returned results, or if this is an update, the same row returned twice with different data.)

b) you are at the beginning of the page, and the new page is behind of the current table scan; the rows which are moved from the current page to the new one will not be read, resulting in matching rows not being returned.

c) your read query isn't a table scan, but utilizes an index, which holds row id's: the change in rowID caused by the page split may result in the row changing and results which don't' match your query being returned.

note: in sql server almost any modification (including deleting rows) can cause a page split.