1

My app needs to batch process 10M rows, the result of a complex SQL query that join tables.
I'll plan to be iterating a resultset, reading a hundred per iteration.
To run this on a busy OLTP production DB and avoid locks, I figured I'll query with a READ UNCOMMITTED isolation level.
Would that get the query out of the way of any DB writes? avoiding any rows/table locks?

My main concern is my query blocking any other DB activity, I'm far less concerned with the other way around.

Side Notes:
1. I'll be reading historical data, so I'm unlikely to meet uncommitted data. It's OK if I do.
2. The iteration process could take hours. The DB connection would remain open through this process.
3. I'll have two such concurrent batch instances at most.
4. I can tolerate dup rows. (by product of read uncommitted).
5. DB2 is the target DB, but I want a solution that fits other DBs vendors as well.
6. Will snapshot isolation level help me clear out server memory?

Improve this question
5
  • Which DBMS are you using? Sybase? SQL Server? MySQL? Commented Nov 11, 2013 at 19:10
  • DB2 (primary), MSSQL, Oracle. Commented Nov 11, 2013 at 19:35
  • Oracle never lets you read uncommitted data and READ UNCOMMITTED is not needed because readers never block writers and writers never block readers in Oracle (and in DB2 since 9.7) Commented Nov 11, 2013 at 20:14
  • @a_horse_with_no_name isn't this a too general statement? won't that depend on the isolation level of the reader and writer a like? Commented Nov 11, 2013 at 20:23
  • No, that's not "too" general. It's how Oracle works. Actually several other DBMS also work that way: Postgres, Firebird, MySQL/InnoDB - even SQL Server if configured properly. It's based on an architecture called MVCC: en.wikipedia.org/wiki/Multiversion_concurrency_control Commented Nov 11, 2013 at 20:46

3 Answers 3

Reset to default
1

Have you actually encountered any real locks on read?

As far as I'm concerned, the only reason that READ UNCOMMITED existed in SQL standard was to allow non-locking reads. So I don't know DB2, but I blindly bet that it does not lock data during read in READ UNCOMMITED mode. Most modern RDBMS systems however don't lock data at all during read (*). So READ UNCOMMITED is either not available (in Oracle, for example) or is silently promoted to READ COMMITED (PostgreSQL).

If you can freely choose the engine, either check DB2 transaction isolation level handling or go for Oracle/PostgreSQL/other.

(*) More precisely, they don't exclusively lock the data. Some shared locks can be placed on queried tables so no DDL alters them during read.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Frankly I haven't yet tried, I'm checking the feasibility of the solution. So DDLs are my only concern? I'm totally fine with that.
1

My answer applies to SQL Server.

Read committed releases lock after every row read (approximately). Locking is probably not your problem.

I recommend you use the safer READ COMMITTED. Better yet, use snapshot isolation. That removes many locking problems. There are disadvantages as well, sou you better read a little about it.

My main concern is my query blocking any other DB activity

Snapshot isolation makes all locking concerns go away for read-only transactions. No blocking either way, full data consistency. Be aware that long-running transactions can cause TempDB to fill with snapshot versions.

The DB connection would remain open through this process.

That's a problem because a network hiccup, app deployment or mirroring failover would kill your batch process.

Be aware, that read uncommitted can cause queries to sporadically fail outright. You need retry logic or tolerate failed jobs.

Improve this answer

2 Comments

What could blocked my queries with READ_COMMITTED? and what would I block? snapshot isolation - While creating the snapshot with READ_COMMITTED won't that lock other transactions? I guess that's also not supported by all DB vendors? Failures - My batch processing knows to how to resume from where it last left of - so that's not a problem. Didn't know about "sporadically fail outright", why? certain DB edge cases?
Read committed with SQL Server generally does not block other transactions because all S-locks are extremely short lived. By coincidence it can of course block other writes for very short durations. Cannot imagine this being a concern for you. Lock-lived X-locks by other trans can block a read-committed reader.; SQL Server snapshot isolation truly does not block, or block others. You can delete an entire table and the snapshot will not block.; SQL Server can abort a query if the next page it expects to see with a read-uncomitted scan of a b-tree index disappears. Without locks that can happen.
0

In sql server Transaction isolation level Read uncommitted cause no lock on table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.