| Author |
Topic |
|
helpme
Posting Yak Master
141 Posts |
 Posted - 2005-02-03 : 16:22:19
|
| I think the default isolation level for an instance is 'read committed'. I believe this is what we want, but is there a way to verify that it is set at this level for the instance. We are having some locking issues that I don't understand why we are having, it's almost like it's set at a higher level. |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-02-03 : 16:40:32
|
BOL: isolation levels, typesRead committed (SQL Server default level).Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).Read committed (SQL Server default level).Repeatable read.Serializable (the highest level, where transactions are completely isolated from one another).  |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-03 : 16:49:04
|
| To verify your isolation level you can use:dbcc useroptionsHowever, Isolation Level will only show in the output if it has been explicitly set using "set":set transaction isolation level <isolation level>otherwise it will be the defalult setting: Read Committed You can still have locking problems with it set to Read committed. Be One with the OptimizerTG |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-02-03 : 16:53:11
|
| I had never set my isolation level so when I ran the DBCC USEROPTIONS the isolation level value did not show up in the list. However after setting it like:SET TRANSACTION ISOLATION LEVEL REPEATABLE READGODBCC USEROPTIONSGONow it does show up in the list |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-03 : 17:04:44
|
| I know, its like:user: "What's your isolation level"server: "you tell me what it should be and I'll tell you what it is."Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-03 : 17:09:28
|
| so, helpme, we can't help you until you help us help you. Are you doing reporting type queries from high contention tables that are used in an OLTP system or what?Be One with the OptimizerTG |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-03 : 18:53:53
|
| Actually right now we are just testing 2 processes. One processes is an update and the other is just trying to read the tables, but is being locked out. Our developers use a case tool that generates the code for them behind the scenes (they don't actually write the 'c' code or the sql statements), so we are probably limited to how much we can change on the coding side. I was checking the serverwide parameters to see if there was anything that might not be set right. I wouldn't know why this wouldn't work with 'read committed', but again, since the case tool is generating the code it may be putting in parameters to set the isolation level to a higher level. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-03 : 19:28:59
|
quote:
since the case tool is generating the code it may be putting in parameters to set the isolation level to a higher level.
I doubt it. Ideally you don't want to do heavy duty reading out of the same db that you're writing to. If you can afford dirty reads, that's the best way to let the writers do their thing and still query the data without locking.Either with "Set Isoloation Level..." or you can use this: From <myTable> with (nolock) for table by table control. However, if you can't afford dirty reads, and can't isolate the reading data from the writing data, the best thing you can do is optimize the heck out of your code to minimize contention.Be One with the OptimizerTG |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-02-03 : 19:36:36
|
| My favorite With (NoLock)!!!If all else fails and you have no control over the code suggest a business process that would avoid these conflicts. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-05 : 10:31:04
|
| we now have one user running a process (granted, it's a very involved process) that has locked several tables. the spid is showing as sleeping/awaiting command in EM. When I go into sql profiler for this spid, it's showing no activity (which would be expected based on EM). None of the processes displayed in EM are showing as blocking or blocked by. How can one individual running a process lock themselves out? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-05 : 11:00:18
|
| What exactly are your "locking issues"? if nothing is blocking or being blocked then what are the symptoms of your problem?Be One with the OptimizerTG |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-05 : 11:08:42
|
| several tables are locked by a process that is showing in EM as sleeping/awaiting command. shouldn't the process be showing as runnable? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-05 : 11:15:31
|
| try running "sp_who2 active" in query analyzer. run it several times and see if diskIO column is changes, look at the command column to see what it's working on, and of course look at the blkby column. What is your process? Is it an SP? or a sql script? or what? Has it ever run to completion? How long have you let it run? Can it be broken into seperate steps, at least temporarily for trouble shooting purposes?Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-05 : 11:33:54
|
| After running sp_who2 active, if you see your process listed with "sleeping awaiting command" then nothing is being blocked. Perhaps your process is an application with an open connection to the server but is not requesting anything at the moment, or has hung, or finished for that matter.edit:Do you have anything in your process that logs progress and errors?Be One with the OptimizerTG |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-05 : 11:51:17
|
| the diskIO isn't changing and the spid in question isn't showing up. I've disconnected and reconnected in EM and it still shows the problem spid with a lock on the tables, but it doesn't show up in QA when running the sp_who2 command. the only processes showing up (besides the 'sa' processes) are the QA session where I'm running the sp_who2 command the the sql profiler that I started on the spid locking the tables. the process is 'c' code generated by our case tool. The process has completed in another environment with another database, but we were trying it with sql server and windows (same tables and indexes). The process was started several hours ago (over 12). In the previous environment it ran about 2 hours. Switching enviroments isn't supposed to be a problem for the case tool, all you are supposed to have to do is re-gen the code (good selling point for the case tool, but it obviously hasn't been quite that simple, at least not in our case). |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-05 : 11:57:41
|
| also, if I try to start another process accessing any of these tables, the new spid shows up when running the 'sp_who2' and shows the blkby column being the spid locking the tables above. But, the spid that is locking the tables above still doesn't show up in the list itself. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-05 : 12:11:51
|
| try the sp_who2 command without the "active". Does the process showup? Since its been going 12 hours now and ran in 2 before, I would try "killing" the process. From EM also check and see if trancount > 0. An uncommitted transaction that hasn't been rolled back yet can foul thing up pretty good.Be One with the OptimizerTG |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-08 : 15:00:56
|
| I have one more question here. I can start a process and go into EM, and when I'm looking at the locks/object, I can click on one of the tables and it will list several entries for the same process ID, and it lists a value under the index column that is the primary key of a totally different table.1) with only one process running, why would there be so many entries here on the same table for the same process? 2) why would these entries reference an index on a different table from the one that was clicked?ok, it's 2 questions instead of 1 :-) |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-02-11 : 07:34:12
|
| Does anyone have any thoughts here? Any replies are appreciated. |
|
|
|
|
default isolation level