Database Wars: Oracle Vs SQL Server Lock Escalation
Jul 20, 2005
I was at a conference and had it asserted to me by an Oracle
afficiando that Oracle and DB2 handled low-level locks "better" than
SQL Server, and that this was likely the cause of SQL Server's
relatively slower and more deadlock-prone performance when running the
same application. (SQL does seem to perform more poorly for this app,
a PeopleSoft customer service and billing app.)
Is there any significant difference in lock escalation strategies
between major databases, and if so, what are the implications?
Deletion statement with not in clause will escalate table loack or any kind of lock ???
Suppose i am selecting one table.It has locked at that time. is it possible to find what kind of lock occured on this scenario ?. SQL SERVER STORES THIS ANY TABLE ???
While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this. Is there any way to find out why lock escalation in such place occurres?
Statement is like:
delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids
When I run the SQL Profiler to detect lock escalation, I noticed it detected a lot of lock escalation with the textdata indicating "create procedure" on certain stored procedure.
AppDomain 446 (A.dbo[runtime].477) unloaded. AppDomain 446 (A.dbo[runtime].477) is marked for unload due to memory pressure. Error: 6532, Severity: 16, State: 48. .NET Framework execution was aborted by escalation policy because of out of memory. I€™m getting this problem on server €œA€? when I restore a backup that is located on a drive on server €œA€? to server €œB€?. The drive isn€™t actually on server €œA€? it is on an EMC but is mapped to server €œA€?. So, I guess server €œA€? is acting as a file server for the file. Server €œA€? is an x64 bit server with 32 GB of Ram. It has SQL 2005 Enterprise Edition build 9.00.3042.00. Min Memory is set to 2 GB and Max is set to 28 GB. SQL service is running under a windows account with €œLock pages in Memory€? set. From what I€™ve read this was a problem but I thought SP2 fixed it. Any ideas? Thanks.
I have a SQL CLR used to read a XML file, it works fine when the file is smaller, but will give me this error if the file if bigger i.e. more than 10M.
Msg 6532, Level 16, State 49, Line 2
.NET Framework execution was aborted by escalation policy because of out of memory.
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink sink, Int32* pcbLen, IntPtr* ppwsName)
at Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink eventSink)
at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()
at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at BuildDirect.SQLCLR.CLRErrorHandler.RaiseNewCLRError(Int32 errorid, String errormsg)
at BuildDirect.SQLCLR.CLRErrorHandler.ReportCLRError(String location, String errormsg)
at BuildDirect.SQLCLR.CLRErrorHandler.ReportCLRError(String location, Exception ex)
at BuildDirect.SQLCLR.HTTPRequest.uCLRGetHTTPResponse(String url)
i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.
My oracle server host is dbserver.hari.com.edu and its name is Prod (name of the machine)
i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.
My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.
Hi there, I have a problem of exporting the SQL Server 2000 database to Oracle 8i. Can anyone help regarding this?? Please help.... Thanking you in anticipation RGDS Amit
Error: The Script returned a failure result. Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out. Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected) Data access (selected) RPC (not selected) RPC Out (not selected) collation name connection timeout query timeout
In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.
To accomplish this,
1. the ORacle Admin has created a view of the Oracle database for me. 2. I have installed the Oracle client on the SQl server box. 3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database 4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully 5. When I go to query the database, I get the error below:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.]
I just wanted to know what the easiest way would be to migrate a SQL Server 2012 database to Oracle?
Apart from the tables and views, the database also uses a number of stored procedures and functions which will also need to be migrated over onto Oracle.
I guess the problem with generating scripts for the database (right clicking database > tasks > generate scripts) is that the TSQL scripts will not run in Oracle which uses PSQL? Is this correct?
I have also considered using the import/export wizard. Do I need to install a feature to use this to export to Oracle?
I am having an issue trying to get a linked server to work from SQL Server 2000 SP4 to an Oracle 10.1 database. This server runs Windows 2003 Server.
I had first installed the Oracle client 10.2 on the server and was able to access the Oracle db using the client.
However - when I set up the linked server on SQL Server - and click the "Tables" underneath the linked server - I get the following error.
OLE DB Provider reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned]
I uninstalled the 10.2 client and put the Oracle 9i client on the server (on a suggestion from a SQL Server DBA to put an earlier client on the server) - still able to connect using the Oracle client - but not via SQL Server.
I have checked to make sure the MSDAORA.dll is registered. I have also checked that the account that SQL Server starts under has proper permissions to the Oracle folders (that login has admin permissions on the server).
I have named the Linked Server the same name as the service name in tsanames.ora.
I'm at a loss - any ideas? Help - i'm tearing my hair out here.
One annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...
"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)" ...
I am using SQL Server 6.5 and this database has 50 database tables and each table has 10000 records in it. So, in short it is very big database. Very often I get the "Database Lock" problem. I tried to tune the database but no help. Is it like that if I upgrade SQL Server 6.5 to SQL Server 7.0, will it help me ? If yes, how ?
In my system I got a problem. One of the database's Tables are not getting opened and I can not even alter a Stored Procedure which is related to the tables of that database. How can I solve that? Subhasish
I may not sound desparate, but I really am. I have spent many days now trying to install and re-install Microsoft SQL 2005 Express. I still cannot connect to the database! I have read this forum and tried various suggestions, like setting the remote connections and TCP/IP + Named Pipes. Still doesnt work!
The environment is Dell Dimension 24000 (I86) with WinXPPro.
Out of desparation, I downloaded and installed Oracle 10g XE. It works just fine !
My question is: Does the Oracle database server interfere with connecting to the Microsoft SQL 2005 Express server ?
Also, I notice that there are an awful lot of questions posted on this Forum concerning just getting your software up and running. Are there that many stupid folks out there like myself, or has this application been released prematurely ?
Hi We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Hi,I need to lock a database (prevent users from connecting) in order toupdate it. I already know how to kick everyone out with their spid buti can't figure how to prevent them from reconnecting.Thanks !
How do I lock entire database? I want an exclusive lock on the db by a user who is the dbo of that database only (not sa user).
The scenario is we have a web application and each week we need to do data uploads (with etl). During this upload, the users accessing the website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc. Abc is dbo for that database and the etl is also done by abc login. So will db locking help in this case as the website can also read the data being a abc user?
Hi, I have a table in SQL Server 2005 which has [Id] and [Name] as its columns. I also have a Oracle database which has a similar table.
What I want to do is as follows: In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
