0

I seek some guidance regarding transactions. The case:

I have table with a few regular fields and 2 foreign keys. There's a unique index over the 2 foreign keys. The table has several 100.000's records. Two threads: Thread 1 is some maintenance process that gathers a fairly large chunk of data, deletes all records where FK1 = and then reinserts several thousands of records all with that same FK1 = . It takes well over a minute to delete and inserts these records. During this minute a few dozen times a 2nd thread (never a third) is started that might also update or insert a record where FK1 = . When this happens during the work of thread 1, a unique index violation sometimes occurs.

What can/should I do regarding transactions for thread 1 & 2, what isolation level. Any guidance is welcome!

Thanks in advance, Jan.

Jan C. de Graaf
  • 171
  • 1
  • 6
  • After reading some more it seems that setting the TransactionIsolationLevel to 'Serializable in Thread 1, should protect thread 1 from any actions done by the 2nd threads. Am I right? – Jan C. de Graaf Jun 17 '14 at 06:33
  • Serializable prevents multiple tx to runs together at all cost, that's often not ideal. In your case read-comitted should be fine. Since both threads perform updates, if first tx obtain a FK = ? write lock to perform update, the second tx trying to update FK = ? will wait until this lock is released. – gerrytan Jun 17 '14 at 07:06
  • @gerrytan: Thanks for your comment. In an earlier attempt both thread-1 and the 2nd threads used read-committed transactions. Still a 2nd-thread was able to insert a conflicting record while thread-1 was busy inserting several 1000's records. So, read-committed seemed not strong enough. Apparently the delete action in thread-1 does not lock enough to prevent a 2nd-thread to do an insert. – Jan C. de Graaf Jun 17 '14 at 07:20
  • Then I suggest you review your SQL, did you have proper transaction boundary? In most dbms it should be enclosed in `begin transaction` and `commit`. A common mistake is you forgot this transaction boundary and you have autocommit mode -- hence each single statement is a transaction by its own. Remember running (or failing to run) a single transaction should never change your data into inconsistent state. – gerrytan Jun 17 '14 at 07:21

1 Answers1

0

Since both thread 1 and thread 2 performs update, they should never run together (use read comitted isolation). Instead I'd focus on breaking down thread 1's transaction (tx) into multiple smaller/faster tx.

For example use limiting on thread 1 tx such that you only perform maintenance over the first X rows of FK = ?. Do this over a loop until all FK = ? rows are processed. If one of these tx only need 2-3 seconds then another tx from thread 2 can run before the next batch of thread 1 tx runs.

Pick a quiet time to perform maintenance to reduce user experience impact.

gerrytan
  • 40,313
  • 9
  • 84
  • 99
  • Thanks again. The ~1 minute job is already 1/100th of the total task:) I could split the total task in even more chuncks easily but this will never solve the problem fully because there are dozens of invocations of '2nd-threads' all the time. And yes, normally this large task runs during a quiet time, but sometimes emergency maintenance is required during peak time:( – Jan C. de Graaf Jun 17 '14 at 07:23