Snapshot Isolation - Confused !
Jun 8, 2007
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
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
So, which of the two snapshot isolations do I need for "Oracle-style locking" in SQL 2005 ?
Many thanks!
Andy
View 3 Replies
ADVERTISEMENT
Sep 27, 2005
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.
Thanks
View 3 Replies
View Related
Sep 20, 2007
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
--Select before delete statement
S3 Plastpall trippelkrage 40 6
S3 Plastpall trippelkrage 40 7
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
View 5 Replies
View Related
May 30, 2006
I was reading at the following page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp
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?
View 5 Replies
View Related
Nov 29, 2007
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
View 1 Replies
View Related
Dec 10, 2014
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
Database.ExecuteSqlCommandAsync("exec dbo.spSync_MatchesByTenant @MatchesGroup, @TenantId", parameter, licenseIDParam);
Is it possible the code does not use the read committed snapshot isolation level of the database?
View 6 Replies
View Related
Jun 20, 2007
Hi
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.
View 1 Replies
View Related
Sep 16, 2007
I read about isolation levels... good, how can I set a proper isolation if I have 100 transactions... What is the aproche?:shocked:
View 1 Replies
View Related
Sep 13, 2004
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
View 2 Replies
View Related
Jan 4, 2007
Hi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737
go
use mosaikDB737
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END
use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput
View 5 Replies
View Related
Dec 15, 2007
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed
thank you for any information or help.
View 3 Replies
View Related
Mar 5, 2008
Hi,
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
mySqlConnection.Close()
End If
End Try
End Sub
View 1 Replies
View Related
Jan 12, 2007
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.
View 4 Replies
View Related
Nov 15, 2000
Is there a way to change the default isolation level at the SQL Server level to READ UNCOMMITED ??
View 2 Replies
View Related
Jun 30, 2004
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!!
Howdy.
View 14 Replies
View Related
Sep 16, 2007
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?
View 4 Replies
View Related
Aug 29, 2007
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
View 3 Replies
View Related
May 30, 2006
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
View 3 Replies
View Related
Mar 13, 2000
Hello,
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)
I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah
if @@error = 0
return(100)
else
return(200)
(Idon't ever get back "200")
Any ideas???
Thanks for your help.
View 1 Replies
View Related
Dec 18, 2006
Hi
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
Thanks
KK
View 13 Replies
View Related
Jul 2, 2002
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.
View 1 Replies
View Related
Mar 21, 2007
I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?
select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x
where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y
The Yak Village Idiot
View 4 Replies
View Related
Jun 14, 2007
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
View 2 Replies
View Related
Mar 13, 2008
Hi
I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X.
Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?
TIA
rank beginner
View 2 Replies
View Related
Jul 23, 2005
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
View 5 Replies
View Related
Nov 10, 2006
Hi there,
i need a database for my Windows CE application which i can update from a desktop application.
I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.
So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?
Can anyone give me a hint what the best solution could be?
Im using Visual Studio 2005 and a CE device.
Thanks a lot
View 1 Replies
View Related
Mar 28, 2007
I'm trying to get up-to-speed on developing new websites using Visual Web Designer and SQL Server Express.
I have previously installed various Microsoft web development components (Visual Studio 2005, .NET Framework 1/2/3 and SQL Server 2005). I've also tried out the new Web Expressions Beta (and Design and Blends, altho the install keys for the latter two never worked and I never received an answer as to what to do for that in the appropriate forums).
TODAY, I'm just trying to get Visual Web Developer and SQL Server Express installed so that I can start down the path of "Connecting to an Existing Database" as outlined in the (downloaded) "Microsoft Visual Web Developer 2005 Express Edition - Build a Web Site Now!" PDF gude (page 138: "Start Visual Web Developer and display the Database Explorer window.").
HOWEVER, when I attempt to install "SQLEXPR32.exe" I get the following message after all the unpacking seems to complete:
"SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Franework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme."
NOW, my first confusion is this: is "SQLEXPR32.exe" Server 2005 or SQL Server Express -- and what's the difference? Following this bit of unhelpful error message, I searched through the SQL Server Express pages and found a live link to a "uninstall tool" that hinted that it could solve my left-over garbage problems. However, then I run that I get:
"The setup has encountered an unexpected error in datastore. The action is Write_CommitFlag .... blah-blah-blah"
so it sounds like the automatic uninstall tool has gotten lost.
ODDLY, the codish window which follows contains the following:
"
Reference to undefined entity 'nbsp'. Error processing resource 'file:///C:/DOCUME~1/Kevin/LOCALS~1/Temp/IXP000.TMP/failed....
<!-- div id="RPCreated" style="display:none">
-----------------------------^
" which very peculiarly looks like something has gotten lost and confused over a non-breakable space? (" "). Huh?
ME? I'm totally lost! Is there no step-by-step cookbook that I can use to just start all over and go through the painful (DSL) downloads and get my show on the road?
Sign me ... "depressed on the garden isle of Kauai ..."
View 5 Replies
View Related
Jul 21, 2007
What is SQL?
After running a disk cleanup and defrag, I now have this icon and it tells me SQL is not connected. What is it? I am on a campus which supplies "high speed" internet connection... Please, help! I'm not savvy at any of this!
View 4 Replies
View Related
Sep 12, 2007
There are two properties in using CheckpointFile: CheckpointUsage and SaveCheckpoints:
It says: SaveCheckpoints indicates whether the package saves checkpoints while CheckpointUsage indicates whether the package uses checkpoints.
The confusion is: If CheckpointUsage is set to NEVER, and SaveCheckpoints is set to be YES, is there any checkpoint file saved on disk even if a filename is specified? It is easy to find out through a simple testing, but the teminologies here are kind of confusion.
comments please ?
View 1 Replies
View Related
Sep 5, 2007
I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.
I need to go from this...
PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y
into this....
A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y
Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?
View 3 Replies
View Related
Mar 5, 2007
the field which has drilldown option is always showed minimum width after delopying to website
I attempted to set all properties of this fields as i can,such as "width","can grow" etc, but all do not work
any idea about this?
thanks
View 1 Replies
View Related
Sep 10, 2002
Hello all,
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?
Thanks for any help
Suresh
View 10 Replies
View Related
Jul 4, 2005
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.
View 14 Replies
View Related
