I am getting a resource issue on a server for obtaining locks. Looking for ideas. This is a sql 2000 server running on Win 2003 with 16 gig of RAM on a new Dell quad box with data on a SAN. That's the good news. The bad news it is a 3rd party app which I have almost no ability to change. The users connect through Citrix with about 400 concurrent at a time. I was looking at setting a higher min lock issue. yesterday it sent me the dreaded alert Fatal error in resources with this issue and buried the server at 100 CPU for about 2 minutes. Any help or insight appreciated. Most of my standard sql data looks OK, Cache at about 99, system queue at about .8.
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?
Devteam writes "Hi, WIndows version 5.0 (SP4) I am using sql server 2000. in our application i am using simple select statement to retrieve the value from the database. Sometime the same query is accessed i did not get the result.i have tried to run the same query using query analyzer. but the query is keep on running. at the same time when accessing other rows in the table, it gets retrieve the values.
what is the problem here... is there anything locked that the row before executing the select statement. if there is any lock is present then how to identify it and also how to prevent a row from locking... if the lock is occured then the other transactions also not executed. why the lock is placed in the first place."
Hi All,I have a table that holds pregenerated member IDs.This table is used to assign an available member id to web sitevisitors who choose to register with the siteSo, conceptually the process has been, from the site (in ASP), to:- select the top record from the members table where the assigned flag= 0- update the row with details about the new member and change theassigned flag to 1- return the selected member id to the web pageNow I'm dealing with the idea that there may be brief, high trafficperiods of registration, so I'm trying to build a method (storedprocedure?) that will ensure the same member id isn't returned by theselect statement if more than 1 request to register happens at thesame instant.So, my question is, is there a way, once a record has been selected,to exclude that record from other select requests, within the boundsof a stored procedure?ie:- select statement is executed and row is instantly locked; any otherselect statement running at that exact moment will receive a differentrow returned and sill similarly lock it, ad nauseum for as manysimultaneous select statements as take place- row is updated with details and flag is updated to indicate themember id is no longer unassigned- row is released for general purposes etcIf what I'm suggesting above isn't practical, can anyone help meidentify a different way of achieving the same result?Any help immensely, immensely appreciated!Much warmth,Murray
Hi, i am getting this error when i am running a stored procedure.
Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
i think so it is getting this error becasue it blocking it self at one point in the SP
DECLARE cty_Cursor CURSOR FOR SELECT Country FROM TB_Country
declare @cty varchar(2)
OPEN cty_Cursor; FETCH NEXT FROM cty_Cursor into @cty; WHILE @@FETCH_STATUS = 0 BEGIN EXEC SP_DO_SOMETHING @cty FETCH NEXT FROM cty_Cursor into @cty; END; CLOSE cty_Cursor; DEALLOCATE cty_Cursor;
i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.
how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.
I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.
Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:
use Master go sp_lock go use Experience go sp_lock go
which results in:
spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 1 85575343 0 TAB IS GRANT spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 13 0 0 DB S GRANT 52 1 85575343 0 TAB IS GRANT
My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open
Question 1: The way I read this is: the "use Experience" statement creates a shared database lock. Am I reading this right?
Question 2: I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained. Should I be concerned about these locks? Is this expected behavior?
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
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Hello everyone,I have a web project where users access a aspx page to view information stored in an SQL database.My client want that one user can access a row of information and see it, all other users shouldn't be able to view or update the same row?it means whenever a row of data is displayed by some user, this row should be locked even for beeing viewed by all other users, when this user close this page, this row will be available. ?I should do this in code behind or something in sql...How can I do that???
An application we are designing is behaving rather strangely. Basically, we have a trigger on SQL 2000 on W2K Server watching for a record update. When that happens a stored procedure gets executed which in turn spawns a VB that extracts that record, applies some rules to it, and writes a text file to disk. 9 out of 10 times, this program will cause a CPU spin, and it can't be killed from the Task Manager.
I did not write the VB app, I am writing the front end for the application in ColdFusion, but I need some ideas on what might be causing such behavior and how can the problem can be diagnosed. Thanks for any help!
Is it good practice to use WITH (NO LOCK) on SELECT statements, ie SELECT * FROM MyTable WITH(NO LOCK) Or does the SQL Server optimiser automatically use WITH (NO LOCK) ?
Hi, i have a big problem , i work with sql 2000 on windows 2000. When user do a select against my database sqlserver lock all the table and nonoe can work. Haw can i change the isolation level for a ropw and for all the db. Thanks.
Wondering if anyone might have a guess about this.
I have a small 4 table DB. it's got several stored proc's and it's accessed through .NET to fill it in and get data from it.
It's been working just fine. But this morning, while it was doing its thing, I experimented with it by adding then deleting a View (through the View Wizard.)
Then later I started noticing that my .Net calls had slowed to a crawl (I hadn't made any code changes)and even making direct queries through Query Analyzer had slowed too.
My question is: being that there were no network issues, could the View create/delete have caused the DB to come to a halt or perhaps a table lock-up?
While I'm at it, is there anything that I can put in stored proc's or other places to prevent locking issues (if that's what happened here.)
I already use Begin/Commit Tran pairs.
And sorry if this post doesn't read like a SQL beginner, but believe me, I am.
I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTINAdministrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.
I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTINAdministrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTINAdministrators' because it does not exist or you do not have permission."
What I can (apparently) successfully do is run DENY CONTROL TO [BUILTINAdministrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:
sqlcmd -S (local)MyInstance -d MyDB -Q "select * from my_table"
and see the contents of my_table.
What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?
Hello. I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that. Thanks.
hi all iam working on a ticketing application i want to avoid two users to book the same ticket the requirement is as follows 1. the system should show all the available tickets which is not yet booked 2.when two users book the ticket at the same time time it should not allow the two persons to update at the same tme the main aim is to avoid data concurency how can i get this done
Hi,all: This problem almost drives me crazy, hope I can get some hints from you guyz!!! Ok, here is the situation: I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating. It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!! Thanx in advance!
hi, i have an application that updates some records in sql tables, and i want to do a web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table so i dont have concurrency problems.tnx in advance.
I have a process that is running on a windows service that feeds a web applications database. While the windows service is processing the data the web application can't get to the data, this causes a minute or two delay, no so bad if it didn't happen every two minutes. What I am wondering is if I can set something on the ado.net transaction object that might get me around this problem. I don't know if i want a dirty read, because that would cause the web app to have exceptions. Any tips around this would be great. Thanks in advance
With the Oracle RDBMS, readers never wait for writers and writers never wait for readers. If a SELECT starts reading at 9:01 and encounters a row that was updated (by another session) at 9:02, Oracle reaches into a rollback segment and digs up the pre-update value for the SELECT. A transaction does not need to take locks unless it is modifying a table and, even then, only takes locks on the specific rows that are to be modified.
How does MS SQL Server 7.0 handle this problem? Thanks for any info or pointers!
When I connect to SQL to select(or insert,update) a record in MyTable, I want to restrict nobody can access it. How can I do? Can you give me an example about (or documentation)? Thanks in advance.
I see Lock Timeout events in Profiler but none of the running stored procedures set @@lock_timeout. Select @@lock_timeout returns -1 which means infinite. Why do I see Lock Timeout events when timeout is set to infinite?Please help.
I am getting the message DESCRIPTION: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
it is a 3rd party app I can't change with some ugly sql. My current lock setting is
min max config run locks 5000 2147483647 0 0
Do I need to change the min value to higher? What would be a good value to try, I have 16 gig of memory on the server
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 ?
