Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
idforstuff
Starting Member
6 Posts |
 Posted - 2008-10-14 : 10:12:05
|
| Suppose multiple threads are inserting/deleting/updating/selecting data in the SAME table. But, the constraint is that all threads are working with DIFFERENT rows. What kind of concurrency problems might crop up in this scenario? I suppose the answers would depend on:1. Whether primary keys are defined or not?2. Whether indices are defined or not? If defined are the clustered or not?Can you guys please answer for each one of the above scenarios? I am especially interested to know whether deadlocks could arise here (maybe due to lock escalation)??? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-14 : 11:07:55
|
| That also depends on what isolation level you're using. |
 |
|
|
idforstuff
Starting Member
6 Posts |
Posted - 2008-10-14 : 11:51:47
|
quote:
Originally posted by visakh16
That also depends on what isolation level you're using.
Let us increase the possible scenarios to include implicit transactions vs no implicit transactions. Otherwise transactions are NOT being explicitly issued by the threads. No table hints are being used in queries. |
|
|
|
idforstuff
Starting Member
6 Posts |
Posted - 2008-10-14 : 12:16:50
|
| Actually I would appreciate if someone could just give pointers to books/papers/documentation... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-14 : 12:38:26
|
| Read about Isolation Level, Deadlocking and locking and its type inBooksonline or www.sql-server-performance.com |
|
|
|
idforstuff
Starting Member
6 Posts |
Posted - 2008-10-14 : 13:06:27
|
quote:
Originally posted by sodeep
Read about Isolation Level, Deadlocking and locking and its type inBooksonline or www.sql-server-performance.com
Thank you so much for the response sodeep. Locking and isolation level articles all discuss the problem where the same data is being accessed simultaneously. My problem deals strictly with the scenario where the actual data is different but the table is same. From my limited knowledge, what I see is that the only issue that can arise is deadlocks. Not on table data, but on such things as physical disk pages, threads in the thread pool, memory pages, so on and so forth. But this is just my theory. I would like the experts out there to validate/reject it and possibly provide links to articles/books. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-14 : 13:15:01
|
| But even then in cases like where isolation levels like serializable is used, you can still cause concurrency problems as the transaction which acquires lock prevents other transactions from reading entire range of data over which it acquires lock. So other trasactions have to wait till rows of data that are write-locked by the first transactions are unlocked before they acquire their own locks over them. |
|
|
|
idforstuff
Starting Member
6 Posts |
Posted - 2008-10-14 : 13:22:29
|
quote:
Originally posted by visakh16
But even then in cases like where isolation levels like serializable is used, you can still cause concurrency problems as the transaction which acquires lock prevents other transactions from reading entire range of data over which it acquires lock. So other trasactions have to wait till rows of data that are write-locked by the first transactions are unlocked before they acquire their own locks over them.
Nopes. As I said earlier, a basic constraint is that two threads DO NOT EVER modify the same data!!! Furthermore, as I said above, transactions are NOT being used. Although it would be an interesting discussion all by itself to delve into the intracacies of each of the isolation levels as they impact the problem I have proposed... |
|
|
|
|
|
|
|
|
Multiple threads accessing the same table