Row Lock Versus Page Lock In SQL 2000.
Apr 7, 2004
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?
Please advice. Thanks a bunch.
Pankaj
View 1 Replies
ADVERTISEMENT
Feb 2, 2007
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"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] + "'"
From progress:
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
View 11 Replies
View Related
Jun 2, 2015
What is lock page memory. How it will work and why it will increase the performance.then what is the difference between setting the - max/Min server memory and Lock page memory
View 1 Replies
View Related
May 22, 2006
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.
View 9 Replies
View Related
May 10, 2006
Hi All,
I have seen a few other people have this error.
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".
View 1 Replies
View Related
Apr 11, 2006
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.
View 2 Replies
View Related
May 23, 2001
Hi,
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?
Thank you for any help
View 1 Replies
View Related
Feb 23, 2006
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."
View 1 Replies
View Related
Jul 20, 2005
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
View 12 Replies
View Related
Jun 27, 2007
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.
Regards,
Haroon
View 2 Replies
View Related
Jul 12, 2006
Hi everyone,
I wonder that SQL Server 2000 support the following lokc modes by default or not.
Shared (S)
Update (U)
Exclusive (X)
View 3 Replies
View Related
Nov 20, 2007
Hi Folks,
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?
Thanks Much in advance.
- D
View 3 Replies
View Related
Oct 11, 2007
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
View 3 Replies
View Related
Sep 18, 2006
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???
View 6 Replies
View Related
Oct 6, 2003
I remember there's a command you can type into SP . so no one else can see your SP even the SA. can anyone tell me what's the command is thanks!!
View 3 Replies
View Related
Mar 27, 2001
Hello,
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!
Stas Newdel
View 1 Replies
View Related
Sep 3, 2007
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) ?
View 2 Replies
View Related
May 30, 2006
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.
View 3 Replies
View Related
Mar 29, 2007
Hello,
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.
Thanks for your advice!!
--PhB
View 2 Replies
View Related
Aug 20, 2007
Lets say i have a view.
vuTestingNoLocks
this view looks like this...
Create View vuTestingNoLocks as
SElect *
From dbo.Employees
inner Join dbo.EmployeeTerritories on EmployeeTerritories.EmployeeID = Employees.EmployeeID
If I select from this view using Select * From vuTestingNoLocks (NOLOCK)
Does the (nolock) command propegate down through the tables? Meaning will it scan tables that are locked still ignoring their locks?
View 5 Replies
View Related
Oct 13, 2006
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?
View 3 Replies
View Related
Jul 19, 2006
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.
View 7 Replies
View Related
Sep 19, 2007
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
View 2 Replies
View Related
Jul 23, 2004
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!
View 5 Replies
View Related
Nov 1, 2005
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.
View 2 Replies
View Related
May 18, 2006
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
View 1 Replies
View Related
May 24, 2001
Hi, Everyone
Can anyone tell me how to find who blocks the table or record, because the Lock Blocks is extremly high in the performance monitor.
Regards,
View 3 Replies
View Related
Jul 12, 2001
I was running a query and it didn't execute stating you have run out of locks
How could I set the locks
thanks
Sejal
View 2 Replies
View Related
Apr 26, 2000
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!
View 1 Replies
View Related
Oct 3, 2000
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.
View 2 Replies
View Related
Jul 16, 1998
Do SQL 7 support row lock?
View 1 Replies
View Related
Apr 15, 2003
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.
View 5 Replies
View Related
Apr 26, 2006
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
View 5 Replies
View Related
