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?

Gili Nachum
  • 5,288
  • 4
  • 31
  • 33
  • Which DBMS are you using? Sybase? SQL Server? MySQL? –  Nov 11 '13 at 19:10
  • DB2 (primary), MSSQL, Oracle. – Gili Nachum Nov 11 '13 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) –  Nov 11 '13 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? – Gili Nachum Nov 11 '13 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: http://en.wikipedia.org/wiki/Multiversion_concurrency_control –  Nov 11 '13 at 20:46

3 Answers3

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.

Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60
  • 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. – Gili Nachum Nov 11 '13 at 20:22
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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 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? – Gili Nachum Nov 11 '13 at 20:17
  • 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. – usr Nov 11 '13 at 20:40
0

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

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26