Select With (nolock) OR TransactionScope With Option Read Uncommitted Data
May 29, 2007
Hi Sql gurus :))
I've got a question that I couldn't find a satisfying answer on the net.
What is the difference between:
1) running sql query (select from sth with nolock) with no transaction
2) running sql query (select from sth) withing a TransactionScope with option Read Uncommitted data
Basically, both should do the same work. However is anyone aware of any potential problems using any of both approaches ?
We use 1) to improve our web application scalability since the system works in such a way that any selects and updates on that table (sth) do not interfere with one another.
However, updates are done in a TransactionScope. And when having simultaneous select with nolock and update in a Transaction scope (the select statement has a where clause and
returns records that are not updated by the update statement). However sometimes ( we still cannot figure it out when) the select statement returns some records twice.
For example, the select should return 1000 records , but (sometimes) it returns 1002 records ( the extra 2 records are copies of some of the original 1000 records).
Removing the nolock, makes the problem does not appear - but i want to be 100% sure that nolock is our troublemaker. And if it is - why ?
We also have a problem that this particular nolock select sometimes return even less records than it should.
I know it sounds impossible but it happens.
So anyone who has experience with select with nolock, please share :)
Thanks in advance,
Yani
View 4 Replies
ADVERTISEMENT
Jan 15, 2008
Hi,
Yesterday, we have had a sudden load in our SQL Server 2000 which resulted in several locks. There was not too much time to investigate as we had to rush. A team member had reviewed the processes in EM, Manegement, Current Activity. Looking for blocking processes and killed them.
She told me that as soon as the blocking SPID was killed, another one arose and she had to repeat the operation a dozen of time. When done, the server activity was back to normal. She noticed that more than half of the blocking processes showed that they executed the stored Proc "P_SearchProducts".
We don't own the server and the information on what had happened at that time (batches or resource intensive operations, etc.) is not available for now.
The team suggests that we set the Transaction Isolation Level to Read UNCOMMITTED for this SP. I would like to know better about locks before I go ahead.
P_SearchProducts returns 5 recordsets each one could contains from 1 to 200 rows. To achieve the results, it creates about 10 intermediate tables (SELECT ... INTO #TableX) these temp tables are then used progressively to arrive to the final results. Roughly the volume of these temp tables could be double than the final results. The developer who wrote this SP is not a guru in SQL, there is room for improvement. But here are my questions:
Q1. Could the series SELECT ... INTO #TableX in P_SearchProducts prevent or lock another connection from executing the same SP? If yes, under which conditions?
Q2. Let's assume that P_SearchProducts has a slow execution time. Could it prevent another connection from updating the Product table? And thus leading to a deadlock situation? Something like another transaction (by User2) has obtained lock on most of Product tables, except the Product table which were being slowly read by User1 executing P_SearchProducts. But User1 cannot read the other product tables b/c there are locks by User2.
Q3. If the contention issue was provoked by the slow execution time of many request to exec
P_SearchProducts (let's assume there were suddenly 50 users on the web hitting the search product feature at the same time). Could the Read Uncommitted magically resolve the contention issue, providing we accept the consequences of the dirty read.
Sorry for the long post and thank you in advance for any help.
View 2 Replies
View Related
Apr 23, 2007
1. In this topichttp://groups.google.com/group/comp...b21516252b65e7c,someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED at the beginningof a number of stored procedures and, then SET TRANSACTION ISOLATIONLEVEL READCOMMITTED at the end to minimize the disruption to the application.".My question is, do you really need to set READ COMMITTED at the end ofstored procedure? What scope does that command affect?2. Could someone write some real world example where i should neverread uncommitted data... i'm having trouble understanding when ishould and when i should not use it.
View 7 Replies
View Related
Jul 23, 2005
Is it possible to set READ UNCOMMITTED to a user connecting to an SQL2000 server instance? I understand this can be done via a front endapplication. But what I am looking to do is to assign this to aspecific user when they login to the server via any entry application.Can this be set with a trigger?
View 1 Replies
View Related
Jul 20, 2005
I haven't used the READ UNCOMMITTED transaction isolation levelbefore, and I was wondering if this would be an appropriate use:I have an ID table containing ID numbers that are randomly generatedand need to be unique. There is a stored procedure that potentiallygenerates thousands of these IDs in one execution and inserts theminto the ID table and various other tables. The basic idea is asfollows:Begin TransactionWhile not all IDs generated {GenID:@NewID = GenerateID()If @NewID exists in ID tableGOTO GenIDInsert into ID tableInsert into various other tables}Commit TransactionThe problem occurs when the stored procedure is being run by more thanone process concurrently. The check to see whether @NewID exists inthe ID table will block, waiting for the transaction in the otherprocess to commit.Would this be an appropriate place to use the READ UNCOMMITTEDisolation level to allow different executions of the stored procedureto see what the others are writing into the ID table before thetransactions finish? I only really care that the IDs generated areunique; they're not in sequence or anything like that. Has anyone hadexperience with anything similar?
View 8 Replies
View Related
Mar 1, 2006
Rather than setting by session I would like to configure the DB as readuncommitted.Thanx Advance.
View 5 Replies
View Related
Jul 5, 2005
Are there really any benefit on using Read Uncommitted Isolation Level or having a NOLOCK hints for retrieve queries when the default Isolation level just Read Committed (not using COM+). I'm confused why the Community Server uses this technique perhaps for perf issues but I couldn't see any reason why...
View 1 Replies
View Related
May 12, 2008
Hi,
I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.
1) Should we use NOLOCK with select statements
2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.
Thanks
View 3 Replies
View Related
Feb 10, 2015
I have a stored procedure that updates a table. I also have an UDF that allows dirty reads (nolock).
What's the precedence level in SQL server? If I add xlock,rowlock to the update statement, will the dirty read wait for the update transaction to commit, or will it perform a dirty read regardless of the locking scheme in the update statement?
View 0 Replies
View Related
Dec 5, 2006
We have found an issue with using MSS 2005 with odbc connections, some of
our code inserts data, then reselects the data back with a select using a
different handle. This hasn't caused any issues before but in one customer
this causes a lock up. The timeout error doesn't occur as you would expect
if trying to select data that is uncommitted by another user.
Although obviously we could re-code to avoid selecting uncommitted rows, can
anyone tell me why this works sometimes but not others. Some kind of
setting in MSS that we're unaware of maybe. The code works ok on other MSS
2005 & MSS 2000 servers and oracle & sqlbase.
View 1 Replies
View Related
Mar 11, 2008
Hi, I get an error when select * from a table with nolock....please help.
Cannot specify an index or locking hint for a remote data source
View 3 Replies
View Related
Oct 12, 2004
Hi,
Is there anyway to create view with read only option as in Oracle ? (OR) is there any
workaround for the same ?
Please advice,
Thanks,
Sam
View 3 Replies
View Related
Apr 8, 2008
Hi all,
I just read this article. The kind of select is called dirty read.
So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..
http://www.4guysfromrolla.com/webtech/101100-1.2.shtml
NOLOCK
Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.
View 9 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related
Aug 24, 2007
Hi all,
I'm getting this error on expanding the Databases node in SQL Server Management Studio Express Object Explorer:
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)
A similar error message also appeared when I tried opening an existing data connection within Database Explorer in Visual Web Developer Express. (However, the web application ran fine and it managed to access the database normally.)
These errors only appeared recently. Any ideas how to go about solving this issue?
Thanks in advance.
View 1 Replies
View Related
Mar 20, 2008
We have a stored procedure that failed with: Could not continue scan with NOLOCK due to data movement
We are running with ISOLATION LEVEL READ UNCOMMITTED. There are other jobs running, some of which might be hitting these tables (all using the ROWLOCK hint - though I know that's not guaranteed), however, this stored proc would not be going near the same rows. But even if they were, we'd be happy with either the before-look or after-look. This needs to be a low-impact job and should have minimal impact on ther other jobs, so we can't take out locks. Is there any hint we can use to do this? e.g. can we tell the query to just wait until the data has stopped moving then try again?
View 3 Replies
View Related
Oct 23, 2014
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
View 1 Replies
View Related
Nov 9, 2004
hi, is there any way to select more than two option in the select case sql statement?
i try the sql below, but it give me an error 'Incorrect syntax near the keyword 'if'.'
Thanks in advance.
(SELECT stay = CASE WHEN stay >= 10 THEN '10 stay' ELSE if stay >7 then '7 - 9 stay' else 'no stay' END) AS stay
View 2 Replies
View Related
Mar 24, 2006
Hi Everyone,
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com
Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
View 8 Replies
View Related
Nov 2, 2007
I have mult-value parameter set up for a report and when i preview the report in visual studio 2005, the "select all" option is displayed in the drop down list and works beautifully for selecting all.
When i deploy the report to the intranet website and click on the report, reporting services doesn't show that option to select all. It will however let me click amongst the generated list, but not an option to just click a "select all"?
Is this normal? I suppose i could always just code a value that generates all and add it as one of the selection, but this is more of a functionality question than a coding.
Thanks in advance!
View 1 Replies
View Related
Mar 2, 2007
Hi
Is it possible to set a "jump to report" option where the user can select which report to jump to from a list. E.g
If I click on the Company field it allows me to select to either jump to the company sales report OR the company purchases report
thanks
View 1 Replies
View Related
Jul 12, 2014
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
View 2 Replies
View Related
Jan 22, 2008
I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!
View 4 Replies
View Related
Mar 19, 2007
Hello,
I have several reports that use parameters that I have defined as multi-value. When I am designing the reports I see the "(Select All)" option listed first in each parm control that has the multi-value property set true. However, when the same reports are deployed to our server and viewed through Report Manager, the "(Select All)" option is missing. I need the "(Select All)" to be present.
I've seen some discussion of a service pack. Could this be the problem? What do we need to do to get our "(Select All)" back?
Thanks,
BCB
View 6 Replies
View Related
Oct 19, 2007
Dear friends,
Transaction means the actions user perform in a database, like create table, update, select. Is that correct? Can back up or restore be transactions?
I recently read the tool-kit book and encountered 'uncommitted transactions' many times, esp. in the chapter of 'Backup and Recovery'. For example, today I wanna backup my database by backuping a full database, then three hours later performing a differential backup, and five mins later do transaction log backup. Then I restore those backups following the same sequence. When I restore diferential backup and log backup, I have the option to use one of the three options:
Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)
In the definitions above, 'uncommitted transactions' are all mentioned.
I do not understand why uncommitted transaction happned during or before or after backup( in my opinion, all transactions should be committed before you doing backup)? Can you please give me an example?
Thank you
Julian
View 5 Replies
View Related
Apr 18, 2015
I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
View 7 Replies
View Related
Oct 20, 2007
I am at a loss here. Sub Main() Dim sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ToString) Dim dbCommand As New SqlCommand("test", sqlConnection) dbCommand.CommandType = CommandType.StoredProcedure Using ts As New TransactionScope Using sqlConnection sqlConnection.Open() Try dbCommand.ExecuteNonQuery() Console.WriteLine("Success") Catch ex As SqlException Console.WriteLine(ex.Message) Finally dbCommand.Dispose() End Try ts.Complete() End Using End Using Console.ReadKey() End Sub BEGIN Insert dbo.Table_2 (Column1) Values (newid()) Insert dbo.Table_1 (Column1) Values ('123456')End The Try block catches the exception in the second insert statement as it should, but I still get this exception "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." at the last End Using. I have tried placing Begin Transaction/Commit Transaction around the insert statement, but I get the same exception. What I am doing seems to match all the examples for the TransactionScope class.
View 1 Replies
View Related
Feb 2, 2008
Transaction/TransactionScope
When a user signs up on our site we use Microsofts Membership and Roles Framework. We also have some of our own tables that need to be updated. I have tried to wrap the whole thing into TransactionScope but it is not working. Looking at some samples it might not be possible to do what I am trying to do. We have a Data Access Layer to get the data from or into the database. Here is some mock up code:
using (System.Transactions.TransactionScope transaction = new System.Transactions.TransactionScope()){MembershipUser newUser = Membership.CreateUser(userName, password, email, question, answer, isApproved, out status);Roles.AddUserToRole(userName, Enumerations.Role.Basic.ToString());DAL.Group.Save(user);DAL.UsersInGroup.Save(userID, GroupID)transaction.Complete}
This code is simplified a lot but you get the idea. All these inputs have to complete succesfully to commit the transaction otherwise we have to roll it back. Can I use TransactionScope in this scenerio? The connection to the database happens in the DAL. Lost - help please, newbie
View 2 Replies
View Related
Feb 19, 2007
Hi, I'm trying this feature like:
Using tScope As New System.Transactions.TransactionScope
intRet = ta.Insert(dr.X, dr.Y, .....)
tScope.Complete()
End Using
Works fine without the TransactionScope, but with the above code I will end up with "The timeout period elapsed prior to completion of the operation or the server is not responding." I'm running the development server on my PC and the SQL-2000-server is a machine on the LAN. (Same domain)
View 1 Replies
View Related
Apr 5, 2006
Hi,
I am trying to use transactions as below:
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
//some code here
// Throw an exception to roll back the transaction.
// Call the Complete method to commit the transaction
}
I can't seem to get it working though. Have trided several things like Security Configuration on the MSDTC tab, restarted Distributed Transaction Coordinator? Service, iisreset, different constructors on the transactionscope etc. I haven't tried running it from the same domain since this won't work with the source safe. Is it really suppose to be this "hard" to get it right??
I use Windows XP Professional 2002 service pack 2 and microsoft SQL server 2005.
Thanks for any answers on the matter...
//R
View 6 Replies
View Related
Jan 2, 2007
I am required to insert an xcel file that may contain more than 1000 records into the databse in a single transaction.
I have used Transaction scope for the same.
The code works fine on my machine, Where the database is on a win 2k machine and the application is on Win XP machine. Tha database is Sql server 2000.
When the same code is deployed at Win2k3 server, I recieved following errors,
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Import of MSDTC transaction failed: XACT_E_NOTRANSACTION.
Ther error occured randomly, sometimes after 900 records and sometime after 1500 records.Many times the application successfully insert 1900 records,
Can anybody help me? I think i am required to make some MSDTC settings.
I have checked for the firewall settings. I have increased the timeout of transactionscope as TimeSpan.Max
View 1 Replies
View Related
Nov 15, 2007
I am receiving an error message while using the System.Transactions.TransactionScope class. The error message that I am receiving is "Communication with the underlying transaction manager has failed". This error seems to only appear when I have my web application one server, Server1, and my database on a second, Server2. When I run the web app on the same server as the database (i.e., web site and database on Server2), I don't receive this error. So, this leads me to believe this has something to do with MS DTC. Is there a limit to how much data MS DTC can manage for a given transaction? If so, is it configurable? When I run my code, the application fails after a certain number of steps (this is repeatable). See sample code below. When I execute the code below, the error occurs on UpdateBody2();. If I comment out UpdateBody2(), the error will now occur on UpdateBody3();, and so on. This leads me to believe that I have hit some upper limit. My code follows a pattern similar to this:using {TransactionScope scope = new TransactionScope()){ UpdateHeader(); UpdateBody1(); UpdateBody2(); UpdateBody3(); UpdateFooter();}Where each of the classes follows a pattern of:UpdateHeader(){ using (SqlConnection conn = new SqlConnection()) { conn.Open(); // Do something conn.Close(); } } Environment:ASP.NET 2.0SQL/2005 StandardWindows Server 2003 Thanks.Steve
View 3 Replies
View Related
Jan 31, 2008
I am writing my first distributed transaction application, using C# and running against SQL Server 2005 on a Windows 2003 Server. Both Windows 2003 Server and SQL Server 2005 have their respective service pack 1's applied on this server. I am using Visual Studio 2005 Pro for development. I am using the TransactionScope object in the System.Transaction namespace, and as I understand it, if the SystemTransaction object detects that a connection to a second SQL Server 2005 database is about to be opened, then it will promote the transaction from a lightweight transaction manager to a distributed transaction. I have stepped through the application in the debugger and found that it opens the connection just fine to the to the database, executes the stored procedure like a champ, but when it attempts to execute the second stored proc it generates the following error messages (which I copied out of the output window): ? ex.Message "Communication with the underlying transaction manager has failed." ? ex.InnerException {"Error HRESULT E_FAIL has been returned from a call to a COM component."} [System.Runtime.InteropServices.COMException]: {"Error HRESULT E_FAIL has been returned from a call to a COM component."} Data: {System.Collections.ListDictionaryInternal} HelpLink: null InnerException: null Message: "Error HRESULT E_FAIL has been returned from a call to a COM component." Source: "System.Transactions" StackTrace: " at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)" TargetSite: {Void ReceiveTransaction(UInt32, Byte[], IntPtr, System.Guid ByRef, System.Transactions.Oletx.OletxTransactionIsolationLevel ByRef, System.Transactions.Oletx.ITransactionShim ByRef)}
The Distributed Transaction Coordinator is started on the development machine and the SQL Server 2005/Windows 2003 server machine.
Any ideas?
View 1 Replies
View Related