Multithreading on database server side

Multithreading on database server side

• Lamb

  1. Well, so what happens when I read the table using SqlDataReader and someone else drops this table while I am reading

  2. In case of transactions, maybe I haven't mentioned it, I was thinking about executing the same one, for example,

  DELETE from table1
  DROP table2

  If two threads executes this, the first one deletes some records, so the second one, than the first one drop the table and than the second fails, since the table no longer exists, and is rolled back. Do the deleted records become undeleted
  Maybe this will not work with this example, but only just for illustration...Could two transactions (well, not necessary the same) be cancelled (or not finished as expected) due to their simultaneous execution

  
  

• Robert S Parker

  The database is designed to handle all this for you. Think about it. There is no difference in having a single application with 2 threads and 2 applications with one thread as far as this goes. Databases are designed to handle concurrent requests from 1000s of clients.

  The database handles this via serialization levels and transactions, you should look into the available serialization levels for your database system to understand this better.

  Transactions are designed to be all or nothing. If the transaction cannot complete all the work, all of the work inside the transaction is rolled back. The only issue to watch out for is the deadlock situation.

  Deadlock happens when you lock items in different orders. For example, suppose I start a transaction and update Table1, then update Table2, then commit. Suppose someone else starts transaction and updates Table2, then Table1. In this situation we can deadlock with each other, as I can get lock on Table1 and other guy can get lock on Table2, then we both try to get lock on each other's tables.

  To avoid deadlocks insure that ALL of your code always modifies tables in a precisely ordered fashion. This is easy to do if your app is simple but you have to plan for this if your app is complex.

  
  
  

• Jing

  This will not happen because as soon as either thread1 or thread2 delete the row inside of a transaction, this will block the other thread until the transaction is either completed or rolled back.

  So say thread1 deletes the row and then thread2 attempts to delete the row. Thread2 is completely blocked until thread1 finishes (either commits or rolls back the txn). So say thread1 hits some problem and decides to roll back. Ok now the deleted row comes back, and at the same time thread2 is released to perform the delete. So thread2 successfully re-deletes the row.

  
  
  

• archangel82

  Thank you for reply, this is what I needed to know.

  However I didn't thought the example that way. I will try make it clear:

  I have a function, which do this transaction. Since I am multithreading, it can happen this function to be called simultaneously, resulting this sequence to the db:

  DELETE FROM TableA WHERE id=1
  DELETE FROM TableA WHERE id=1
  DROP TableB
  DROP TableB
  DROP TableC
  DROP TableC
  DROP TableC

  First thread deletes succesfully the row, then the second one starts. This doesn't delete any new rows, can continue and drops the TableB. Now the execution is returned to the first thread. It fails droping the TableB because it is already dropped. The transcation is rolled back, resulting the row to be undeleted.

  Thats it..the result is, the row is back, even when both calls assumes there where deleted.

  I was not able to think of an example where both transcations are rolled back, so I'm asking wheter this can happen. (Simple with not same calls, eg. when another thread interrupts now this two with dropping TableC, the first two are rolled back, resulting te TableB to become undeleted too.)

  
  

• xRuntime

  Check out SQL Server's Books Online, topics:

  Managing Concurrent Data Access

  http://msdn2.microsoft.com/en-us/library/ms189130.aspx

  This is very good at explaining the locking features of SQL 2005.

  
  
  

• Murbro

  By default when you read data this takes a shared read lock so the DELETE FROM TABLE1 would be blocked by the records curently locked by the reader. So the records would delete after the reader has passed over the current page of records. Most likely what would happen is the reader would abruptly run out of records.

  However if you wanted to reduce locking you could try:

  select * from table1 with (nolock)

  In the end, either the reader would run out of records suddenly or display all of them.

  1. DELETE FROM TABLE1

  2. DROP TABLE1

  If 1 happens first, then 2 has no problem with this and drops the empty table.

  If 2 happens first, 1 fails with "table1 does not exist". The delete never completes so there is nothing to roll back.

  
  
  
• Multithreading on database server side