Ruprect asked me in this thread (http://www.dbforums.com/showthread.php?postid=4469852#post4469852) to start a thread on snapshot isolation for questions etc, so here it is.
Please have a read of the excellent whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp
and then post any questions.
My team owns snapshot isolation and the devs who wrote it work for me so I can answer all questions you may have.
I have an application that needs to run on both Oracle10g and SQL Server 2005. What do I need to do for "Oracle-style locking" in SQL 2005 ?
That is, readers & writers never block each other, and you get a consistent view of the database from the point in time your transaction started (e.g. long-running queries across multiple tables shouldn't see committed data from other transactions that started later than this current transaction).
I'm confused as to whether i should be using Snapshot Isolation (SET ALLOW_SNAPSHOT_ISOLATION ON) , or Read committed isolation using row versioning (SET READ_COMMITTED_SNAPSHOT ON).
I think I need snapshot isolation, but not certain. Also, if I turn on snapshot isolation at the database level, is that automatically enforced for all transactions against the database, or do developers have to manually specify SET TRANSACTION ISOLATION LEVEL SNAPSHOT; for every single query they write ? I hope not the latter, otherwise someone is bound to forget!
If it needs to be specified for every query, how does that work for C#/ADO.NET 2.0 ? Is it
Hello all I´m having some trouble with a query I´m using to BCP all rows in a certain table to a flat file. The query looks like this:
Code Snippet USE AVK GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO SELECT * FROM tempProducts GO IF @@ROWCOUNT > 0 BEGIN BEGIN TRANSACTION DECLARE @date char(8) DECLARE @time char(8) DECLARE @sql VARCHAR(150) SELECT @date = CONVERT(char(8), getdate(),112) SELECT @time = CONVERT(char(8), getdate(),108) SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14) SELECT @dt = @date + '_' + @time SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla' EXEC master..xp_cmdshell @sql
WAITFOR DELAY '0:00:10'; SELECT * FROM tempProducts
DELETE FROM tempProducts
COMMIT TRANSACTION END
What I´m trying to do here is copy all rows in tempProducts to my flat file, and the delete ONLY the rows that existed when I entered the transaction. I tried inserting some rows into tempProducts, start this query, insert som more rows (which I can do because of the WAITFOR DELAY), and this is the result.
--Output from first select S3 Plastpall trippelkrage 40 6
--BCP Output NULL Starting copy... NULL 3 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (3000.00 rows per sec.) NULL
So even if only 1 row existed when I entered the transaction, any rows inserted during the transaction are also deleted. What am I doing wrong here? The EXPORT_ORDERS is a view, looking like this:
Code SnippetSELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line UNION ALL SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5), Volume), '') AS Data_Line FROM dbo.tempProducts UNION ALL SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line FROM dbo.tempProducts AS tempProducts_1
When SQL Server accesses the data in a table, it locks the data for the duration of the process to ensure the transactional consistency of the data during the process.
I was wondering if it is possible to take advantage of snapshot isolation to allow me to avoid these locks?
Hi,we are executing the following query in a stored procedure using snapshot isolation level:DELETE FROM tBackgroundProcessProgressReportFROM tBackgroundProcessProgressReport LEFT OUTER JOIN tBackgroundProcess ON tBackgroundProcess.BackgroundProcessProgressReportID = tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFT OUTER JOIN tBackgroundProcessProgressReportItem ON tBackgroundProcessProgressReport.BackgroundProcessProgressReportID = tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportIDWHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND (tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.However, for some reasone we get the following exception:System.Data.SqlClient.SqlException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tBackgroundProcess' directly or indirectly in database 'RHSS_PRD_PT_Engine' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.The exception specifies that we are not allowed to update/delete/insert records in tBackgroundProcess, but the query indeed deletes records from tBackgroundProcessProgressReport, not from the table in the exception.Is the exception raised because of the join?Has someone encountered this issue before?Thanks,Yani
I have several databases set to read committed snapshot isolation level. Tempdb is configured according to best practices, but I don't see it's used much.
The application uses EF6, and it calls the stored procedures in the following way
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
I have SQL Server 2005 installed in Windows 2000. In SQL Server Management Studio's SQL Editor, when I run following:
USE bugdb2 SET ALLOW_SNAPSHOT_ISOLATION ON; SET TRANSACTION ISOLATION LEVEL SNAPSHOT
I get following errors:
Msg 195, Level 15, State 5, Line 6 'ALLOW_SNAPSHOT_ISOLATION' is not a recognized option. Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'SNAPSHOT'.
Hello Every I have a problem with ASP.NET connect to Database Snapshot SQL Server 2005. Step One: I create database snapshot. Step Two: I want to use ASP.NET to Database Snapshot that create already. But I don't know connection string to database snapshot. Please help me ............................ Thank You,
We have an environment production with a lot of activity all days, for this reason the time for the maintenance tasks is very short (only for Reindex and no more).
We must execute all days a DBCC CheckDB command without to affect the performance server and we have a problem with the temporally snapshot that is create during this command because usually we have some load tasks executing at the same time of the DBCC and the size of the snapshot is too big and produce an error for not enough space on the disk.
In some documents we have read that when you have an environment with non NTFS disks the command work like on SQL Server 2000 without snapshot.This is not our case because we have NTFS disks but we would like to execute the command without snapshot, with database online and compatibility level option to SQL Server 2005 (9.0).
One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?
I have a "merge" publication created, with only Procedures, Views and Functions. (Note I have other merge publications that replicate the tables from the same database). I keep getting the same error (see -below) on various procedures. I cannot find anything wrong with the procedures themselves. I also checked the offending procedure by removing it from the publication and compiling it in the database...it works fine. None of my other publications (all tables) encounter this error. * I have 3 others.
My version of SQL is 9.00.1399.06
Here is the message I am getting. Any help would be greatly appreciated. Note I changed the proc name due to client restrictions.
Message: StartIndex cannot be less than zero. Parameter name: startIndex Command Text: select 'number' = convert(int, 0), 'definition' = definition from sys.sql_modules where object_id = object_id(@qualified_object_name) union all select 'number' = convert(int, procedure_number), 'definition' = definition from sys.numbered_procedures where object_id = object_id(@qualified_object_name) Parameters: @qualified_object_name = [dbo].[pra_merge]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script() at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.MergeSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006) Get help: http://help/52006
Source: mscorlib Target Site: System.Text.StringBuilder Remove(Int32, Int32) Message: StartIndex cannot be less than zero. Parameter name: startIndex Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader) at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
I am using snapshot replication to copy data from one database to another. I truncate the destination table first. The publisher and the subscriber are different databases on the same server. The data turns into 1 unrecognizable row.
Here is what the source table looks like: displayTypeID, displayType 1, Title List Formats 2, Title Details 3, Title List Export 4, Pricing Notes 5, clone Mask 001 6, clone Mask 002 7, clone Mask 004 8, clone Mask 008 9, hide From Admin 10, not Common Attr 11, Insert Title 12, title Types
The resulting data looks like this: DisplayType, displayTypeId , 1660953600 I see no errors in replication and nothing in the source table looks like bad data that could cause such a malady. I see the problem on two different servers now and identified the source of the problem, the column order is reversed in the source and destination tables. The data types do not even match so I would at the very least expect an error from bcp.
I am having trouble setting up my Pull Subscription and I am new to replication.
I have several servers hosting a databased website that will be the same, except for user input and traffic. Quite simply, I need to copy most tables, SPs and data from network to network. I can't use FTP/Web synch ... as I mentioned the networks do not touch eachother or the internet.
On server Web1, it was easy to create a Publication called Pub via the wizard for my database: TheDB. Then on Web1, again, I added a Subscription to the Publication, indicating my second server, Web2, and the same database name: TheDB (I have already backed up and restored TheDB to all my servers). Here's one of the sp's I ran on Web1:
I copied the snapshot folder, ie. 20070709134423, onto CD and moved it into Web2's default replication folder, but I always receive: cannot connect to Distibutor. I've tried using an Alias, as well, but don't understand exactly how I should point that either. I checked the publication's PAL and my Web2 user has rights and is an owner of the Web2 TheDB database.
We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).
We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.
The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???
Hi - I hope some of you can help me with this problem.
I'm trying to run the snapshot agent for 77 published tables, but the agent stops suddenly uninterrupted. It stops in the middle of taking a snapshot of a table containing 81,719,531 rows. The error message is pasted below.
Message 2006-06-14 05:33:33.53 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57200000 total rows copied) 2006-06-14 05:34:12.57 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57300000 total rows copied) 2006-06-14 05:34:51.23 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57400000 total rows copied) 2006-06-14 05:35:29.99 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57500000 total rows copied) 2006-06-14 05:36:09.82 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57600000 total rows copied) 2006-06-14 05:36:49.38 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57700000 total rows copied) 2006-06-14 05:37:28.56 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57800000 total rows copied) 2006-06-14 05:37:35.36 [89%] The replication agent was shut down in response to an abort request.
Does anyone know what is causing this error and how I can possibly solve it?
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
lets say user1 is reading row1, then user2 reads and updates row1, when user1 is about to update row1 i want him to be informed that his copy of row1 have been updated, so he has now the options to either get the new version of row1 or cancel his update process.
Hi, folks. Please guide. I have a VB application that is used for production and reporting. I 've been having alerts for deadlocks that popup after every 2 or 3 minutes. I am planning to seperate reporing server by using transactional replication from production server to the reporting server. However some reports update and insert data so i need reporting server to be enabled for DML.
Is there any option on the server-level where i can force each user to operate in READ-UNCOMMITTED mode instead of specifying WITH (NOLOCK) in the queries of my application. Dirty reads won't bother me in current situation, i guess the propotion of fast reads would be a better trade-off. New to SQL, Thanx for helping!!
Hi all. I have a question. I 've read already about isolation lavels, but I don't understand how in practic set proper isolation if I have say 100 transactions..what is the algoriphm?
What is the TRANSACTION ISOLATION LEVEL settings for MSSQL like the default setting in Oracle. In Oracle the default setting allows one session to read consistent data without waiting for the other sessions to commit/rollback the data.
For eg: In Mssql, if I update table A in the first session, and in another session (second session) if I select from table A, the second session waits till the first session completes the updates and commit or rollbacks.
But in Oracle , if I update table A in the first session, and in another session (second session) if I select from table A, the second session will perform a read from the ROLLBACK SEGS and give a read consistent data without waiting for the first session to commit or rollback the transaction.
Is this type of behaviour is possible is MSSQL. And If YES how can I do it?
Hi all, can anyone give me more information on set transaction isolation level serializable ?? I want to prove some lock to use on online insert and update. Thank you every much.
Not sure if this is more a .Net question or SQL Server, but I think it belongs here.
I have a small .Net app that reads records from a bunch of files from disk and inserts them into a database table. There could be several hundred files resulting in 100,000 records or more each time its run. Since it's a large table there are of course a few indexes on it so the insert takes a while. For larger sessions it could run as long as an hour. I need it to run in a transaction so that if anything happens while it's running the records from that run were committed on an all or nothing basis. However, I don't want to lock the table at all while the insert is happening. These aren't transaction records or anything like that, and the batches are separated by client so there will be no conflicts (no need to lock the table).
Unfortunately, no matter what I use for the isolation level of the transaction the table always ends up locked for reads. Data from previous runs is live at this point and we can't allow that. I have the choice of the following isolation levels when I create the transaction, but none seems to work: Chaos ReadCommitted ReadUncommitted RepeatableRead Serializable Snapshot Unspecified
I would expect Chaos, ReadUncommitted, or Snapshot be okay here, but I can't seem to get it working. Any thoughts?
I am redesigning an application that distributes heldesk tickets to our50 engineers automatically. When the engineer logs into their window astored procedure executes that searches through all open tickets andassigns a predetermined amount of the open tickets to that engineer.Theproblem I am running into is that if 2 or more engineers log in at thesame time the stored procedure will distribute the same set of ticketsmultiple times.Originally this was fixed by "reworking" the way SQL Server handlestransactions. The original developer wrote his code like this:-----DECLARE @RET_STAT INTSELECT 'X' INTO #TEMPBEGIN TRANUPDATE #TEMP SET 'X' = 'Y'SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERIDIF @RET_STAT <> 0ROLLBACK TRANRETURN @RET_STATENDCOMMIT TRAN-----The UPDATE of the #TEMP table forces the transaction to kick off andlocks the row in table TICKETS until the entire transaction hascompleted.I would like to get rid of the #TEMP table and start using isolationlevels, but I am unsure which isolation level would continue to lockthe selected data and not allow anyone else access. Do I need acombination of isolation level and "WITH (ROWLOCK)"?Additionally, the TICKETS table is used throughout the application andI cannot exclusively lock the entire table just for the distributionprocess. It is VERY high I/O!Thanks for the help.
Hi,I have 1 SQL statement selecting data from various tables and updating othertables.The question then is how do I prevent other applications from modifying thetables that I'm working on (that is while my transaction is being executed)?I know that the isolation level should be either REPEATABLE READ orSERIALIZABLE. But I need confirmation on if one of these actually solve myissue - prevents other applications/threads from modifying/inserting datainto the same tables that I'm working on.Thanks in advance,Daniel
I have an issue in one of my stored procs. I set the Isolation level to read uncommitted at the beginning of the proc and then I try to reset this isolation level back to read committed. When reset the isolation level, I get and error. has anyone encountered this before?
lets say user1 is reading row1, then user2 reads and updates row1, when user1 is about to update row1 i want him to be informed that his copy of row1 have been updated, so he has now the options to either get the new version of row1 or cancel his update process or continue his update
by the way, im using typed dataset on my data access layer.
Is there a way to define Connection Manager with Read Uncommited isolation level? I do not want to specify (nolock) in all my commands and instead want to give a generic defenition at the Connection level.
