Hello, In MS SQL Server is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON; I cannot find a simple way to detect this in either T-SQL or via the Management Studio's GUI. TIA ...
How long should it take to run ALTER Database [mysite] SET READ_COMMITTED_SNAPSHOT ON I just ran it and its takin 10 minutes. How can I check if it is applied? ...
I need to programaticaly enable READ COMMITED SNAPSHOT in SQL Server. How can I do that? ...
In one of the Stackoverflow podcasts, I remember Jeff Atwood saying that there was a configuration option in SQL Server 2008 which cuts down on locking, and was kind of an alternative to using "with (nolock)" in all your queries. Does anybody know how to enable the feature he was talking about, possibly even Jeff himself. I'm looking a...
I have a query editor (Toad) looking at the database. At the same time, I am also debugging an application with its own separate connection. My application starts a transaction, does some updates, and then makes decisions based on some SELECT statements. Because the update statements (which are many and complex) are not committed yet,...
Does table constraints execute in the same transaction? I have a transaction with Read Committed isolation level which inserts some rows in a table. The table has a constraint on it that calls a function which in turn selects some rows from the same table. It looks like the function runs without knowing anything about the transaction ...
When SQL Server Books online says that "Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction." Assuming we're talking about a row-level ...
I'm writing some logging/auditing code that will be running in production (not just when errors are thrown or while developing). After reading Coding Horror's experiences with dead-locking and logging, I decided I should seek advice. (Jeff's solution of "not logging" won't work for me, this is legally mandated security auditing) Is the...
I'm part of a team building an ADO.NET based web-site. We sometimes have several developers and an automated testing tool working simultaneously a development copy of the database. We use snapshot isolation level, which, to the best of my knowledge, uses optimistic concurrency: rather than locking, it hopes for the best and throws an ...
Hi, I want to know that what is the best way to arrive at the isolation level of the transaction? This is a good link of the available ISOLATION levels. Blockquote It will be nice if someone can explain the various isolation levels of a transaction ...
Hello everyone, For the same ADO.Net statement, I want to make sure my understanding of isolation level and lock is correct. In default SQL Server isolation level (read committed), after read each row, the row will unlocked; If I raise isolation level to repeatable read, the lock (on the whole table? or some other level lock?) will be...
Hello everyone, I am studying snapshot isolation level of SQL Server 2008 from the below link. My confusion is, http://msdn.microsoft.com/en-us/library/ms173763.aspx It is mentioned "Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transact...
I have a web application running in Tomcat 6, and I've managed to configure it to use the built-in DBCP connection pooling, and all is working very well, however I suspect it is running in the wrong isolation level on the database. I'd like it to run in read uncommitted, but I think it's running in read committed and don't know how to se...
Hi. we have a customer that's been experiencing some blocking issues with our database application. We asked them to run a Blocked Process Report trace and the trace they gave us shows blocking occurring between a SELECT and UPDATE operation. The trace files show the following: The same SELECT query is being executed at different iso...
I have an oledb-connection from clickview to a sql2005-server and I would like this connection to use transaction isolation level of read uncommitted. My seconde choice would be to set it on the user. How can I accomplish this? ...
Consider a table with 3 columns: ID (unique, taken from an Oracle sequence), CATEGORY and CODE (no constraints on these last two). Each category has multiple codes attached to it, but the codes must be unique within that category. Example: ID CATEGORY CODE 1 1 X 2 1 Y 3 1 Y //wrong The ...
Simple question? Why is READ_COMMITTED_SNAPSHOT not on by default? I'm guessing either backwards compatibility, performance, or both? [Edit] Note that I'm interested in the effect relating to the READ_COMMITTED isolation level, and not the snapshot isolation level. Why would this be a breaking-change, as it holds less locks, and stil...
Hi there, I'm working on a web app connected to oracle. We have a table in oracle with a column "activated". Only one row can have this column set to 1 at any one time. To enforce this, we have been using SERIALIZED isolation level in Java, however we are running into the "cannot serialize transaction" error, and cannot work out why. ...
I'm a bit confused by the documentation here. I have a transaction, which start transaction does some updates does some selects does some more updates commit I want my selects at step 3 to see the results of updates in step 2 but I want to be able to roll back the whole thing. read committed seems to imply that selects only show ...
Hello All, I know the default TRANSACTION ISOLATION LEVEL in SQL Server is "read committed". If I want to change it to "READ UNCOMMITTED", how may i make this configuration change? note: I cannot use SET GLOBAL TRANSACTION ISOLATION LEVEL, which only apply in the current session. I cannot add NOLOCK in the queries because there are ...