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 |
|
ogren
Starting Member
4 Posts |
 Posted - 2007-03-22 : 14:44:04
|
| I'm writing a web service using .NET 2.0 that can receive many requests concurrently. They all make som updates and inserts into a database.The problem is that sometimes a get an error saying that a command got a deadlock and that I should run the transaction again.All updates use their own connection object with a single transaction. I also use IsolatedLevel to Serializable.I guess this means that if one transaction is in progress, others will immediately fail. But I want the other transactions to wait until the first has finished (or until they time out). Is that possible?I would appreciate any help. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-22 : 16:13:32
|
| Deadlocks do not occur simply because there are concurrent transactions. They occur because transaction 1 has a lock on resource A, and transaction 2 has a lock on resource B, then transaction 1 tries to take a lock on B and transaction 2 tries to take a lock on A and they end up both waiting indefinitely. At the simplest level you can avoid deadlocks by making sure that transactions always lock resources in the same order. In the previous example the problem arises because transaction 1 locks A, then B, while transaction 2 locks B, then A. If they both locked A, then B, one of them would get its locks and complete while the other waited and then the second one would get its locks and complete, without a deadlock.For more seehttp://www.sql-server-performance.com/deadlocks.asp |
 |
|
|
ogren
Starting Member
4 Posts |
Posted - 2007-03-23 : 04:36:16
|
| Thanks for the answer. I don't lock any tables at all. I thought that setting the IsolationLevel to Serializable would make sure that as long as one transaction is in progress, no other transaction could begin. I guess I have misunderstood the purpose of Serializable IsolationLevel. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-23 : 12:25:07
|
| "would make sure that as long as one transaction is in progress, no other transaction could begin"No, it doesn't mean another transaction cannot begin, just that no other transaction can access the data - which is done by locking the data.From Books Online - "The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems." and "This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary."See(2005) http://msdn2.microsoft.com/en-us/library/ms173763.aspxhttp://msdn2.microsoft.com/en-us/library/ms189122.aspx(2000) http://msdn2.microsoft.com/en-us/library/aa259216(SQL.80).aspx |
|
|
|
ogren
Starting Member
4 Posts |
Posted - 2007-03-23 : 14:05:18
|
| Thanks. I realize I have to study the subject a little more. |
|
|
|
ogren
Starting Member
4 Posts |
Posted - 2007-03-27 : 01:55:37
|
| I really need some more help. I don't see how to use the locks. In my transaction, I call a few stored procedures, all of which are on the formIF (EXISTS ...) UPDATE ...ELSE INSERT ...They each operate on a single table. I think it's a TABLOCKX I should use to make sure no other transaction runs the same stored procedure concurrently, but I have only found how to use it during a single SELECT statement.Is it possible to lock the table during the entire stored procedure call? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-27 : 11:59:41
|
| You don't need to worry about trying to make no other run the stored procedure, your concern is locking the data not the code.So in the EXISTS check, use a HOLDLOCK hint to lock the data referenced by the SELECT. That's all you need to do, it will make other transactions wait - but watch out for deadlocks when you do that.See this article for morehttp://www.sql-server-performance.com/rd_table_hints.aspAlso, search for "upsert" on these forums as there has been lots of discussion about what you are trying to do. |
|
|
|
|
|
|
|
|
Concurrent database access