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 |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2007-01-15 : 19:59:11
|
| I have the following three statements that insert, delete and query the same table. Consider a scenario where multiple clients connect and execute any of the three statements. In this scenario, it takes several seconds to execute the last statement and it becomes a real performance bottleneck. Under normal conditions the third statement runs in milliseconds, but under heavy load it takes up to 60 seconds to complete! So, I assume the problem is lock contention.We are using default (READ COMMITTED) transaction isolation level.Is there anything I can change in the following statements that will help optimize the third statement and reduce lock contention?Thanks.Alec1) INSERT INTO DB_ACTIVE_TRANSACTION (TIMESTAMP , APP_SERVER_TAG) OUTPUT Inserted.COMMIT_NUMBER VALUES (GETDATE(), N'myserver');2) DELETE FROM DB_ACTIVE_TRANSACTION WHERE COMMIT_NUMBER = 123AND APP_SERVER_TAG = N'myserver'3) IF EXISTS (SELECT * FROM DB_ACTIVE_TRANSACTION) SELECT MIN(COMMIT_NUMBER)-1 FROM DB_ACTIVE_TRANSACTION ELSE SELECT IDENT_CURRENT('DB_ACTIVE_TRANSACTION') |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-16 : 07:18:14
|
| Why don't you use SCOPE_IDENTITY() function instead?Peter LarssonHelsingborg, Sweden |
 |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 10:56:33
|
| Given that DB_ACTIVE_TRANSACTION is the only table with an identity column, SCOPE_IDENTITY() will return the same value as IDENT_CURRENT(). So, I guess I could use it instead.1. But how would that improve the situation?2. Could anybody explain what kind of locks each of the three statements in my first post impose on the DB_ACTIVE_TRANSACTION table? 3. Which statements will block on each other under READ COMMITTED transaction isolation level? My guess is that statement 3 will contend for locks with statement 2, but not 1.Thanks.Alec |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-16 : 11:01:10
|
| Well, you have a multisuer environment.See this taken from Books OnlineIDENT_CURRENTReturns the last identity value generated for a specified table in any session and any scope. SCOPE_IDENTITYReturns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.Peter LarssonHelsingborg, Sweden |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 12:17:58
|
| Thank you, I have read the BOL description of both functions.I have multiple clients updating server database tables. Each client needs to know the LAST identity value inserted in DB_ACTIVE_TRANSACTION by ANY other client. Therefore, I don't think that SCOPE_IDENTITY will work in my case.I appreciate your feedback.I just browsed through sp_who2 and SQL Server Profiler results and confirmed that there are a lot of clients trying to executed statement 3) and are blocked by some client trying to execute statement 1) or 2). Is there a way to reduce this lock contention on DB_ACTIVE_TRANSACTION table?Thanks.Alec |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 13:11:39
|
| According to sp_who2. I have process 153 running the INSERT (statement 1). Process 100 is trying to execute SELECT (statement 3) and is blocked by process 153. I also have about 20 processes, which are trying to execute SELECT (statement 3) and are blocked on process 100.1. I don't understand why INSERT blocks SELECT in READ COMMITTED mode? Shouldn't this mode allow phantom reads?2. What's even more interesting is why SELECTs block SELECTs?Help, anyone? |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 16:00:11
|
| Ok, after speinding the whole morning trying to chase this one down it turned out that IF EXISTS (SELECT * FROM DB_ACTIVE_TRANSACTION) was conflicting with INSERT INTO DB_ACTIVE_TRANSACTION. Note that SELECT will probably do a full table scan, which is not required. Changing "SELECT *" to "SELECT 1" fixed the locking problem.I hope this will save somebody else's morning :) |
|
|
|
|
|
|
|
|
Help with query