Could an query with READ UNCOMMITTED isolation level cause locks on the tables it access?

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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