Row Level Lock In Sql Server 2000
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
ADVERTISEMENT
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
View Related
Aug 3, 2006
Hi,
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?
Thanks,
Tanmaya
View 3 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
Aug 28, 2007
Hi
I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?
Thanks
View 7 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
Jan 6, 2006
Hallo Everyone,
I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work
Any ideas?
Nigel...
View 12 Replies
View Related
Apr 7, 2008
We had a disaster last week (SAN Corruption) and it hit a bunch of my sql serves.
I have been able to recover all but one. The one I am having a problem with is a NAMED Instance that I obviously don't know the original SP level on. When I try to restore MSDB it won't let me because of a version conflict
Is there any way to tell what the SP Level is either from a system DB .mdf or LDF file or from a backup file without restoreing?
Right now I'm installing SQL Server to a test server and I'm going to try and restore the system dbs at each patch level.. seems like there must be a better way!!
View 5 Replies
View Related
Feb 28, 2007
As an 'SA' I thought that you have permission to do anything on the server. Running an update command from Query Analyzer is throwing this error:
SQL Server 2000 SP3
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
The table is in the dbo schema.
Has anyone seen this error before?
View 12 Replies
View Related
Jul 20, 2005
I have a stored procedure that takes 18 hours to complete. SometimesI get the below error message when I run the stored procedure.'Server : Msg 9002, Level 17, State 6, Line 1 The log file fordatabase 'Customer' is full. Back up the transaction log for thedatabase to free up some log space.'.I checked my log file and it is not full. And I don't know why I amgetting this message. Any help is appreciated.Thanks,Anjula
View 2 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
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
Jul 20, 2005
Hi,We have encountered deadlock on a table which is used to generatesequential numbers for different categories eg typical entriesCategory ValueTRADE_NO 1456JOB_NO 267.....The applications reference the relevant category applicable to themand updatethe Value accordingly. This is table is very small, occupying 1 page.However, it has no index as it was not seen to be appropriate for atable this size.However, can someone please advise whether1. An index is required for row level locking2. If an index on a table as small as above is likely to reduce thedeadlock rate.Also, please consider the following but which I am not sure isrelevant for above query.We noted that when we migrated the database concerned from SQL 6.5 toSQL 2000, using DTS, that the database was NOT strictly in SQL 2000format for non clustered indexes (NC) ie the clustered key was notpart of the NC index until the clustered index was rebuilt.Given this should I just rebuild this table with a fake index and dropit thereafter.We are aware of the different techniques used to avoid deadlocks (egtables accessed in same order etc) and have , as much as possible,implemented those practices.I thank you in advance for any help you may be able to offer.ThanksPuvendran
View 12 Replies
View Related
Aug 3, 2006
Dear Freinds,
we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.
Note :Is Database base level trigger is possible on SQL Server 2000?
Looking for your reply.
With Cheers :beer: ,
Sathesh.M
View 3 Replies
View Related
Jul 20, 2005
HiIs it possible to force row level locking in one or more tables insome database. We have some problems when SQL Server decides to choosepage- or table-level locking.We are using SQL Server 2000.Best regardsAarno
View 1 Replies
View Related
Oct 17, 2007
Hello,
Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?
Thanks.
View 2 Replies
View Related
Jul 28, 2006
I did an in place upgrade of my SQL 2000 server to SQL 2005.
Now all the system and user databases are set to compatibilty mode 80 and in the options tab there is no compatibilty mode 90 option.
How can I set the databasbes to compatibility mode 90?
Thanks!
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
Jan 16, 2008
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
View 1 Replies
View Related
Nov 19, 2007
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
View 1 Replies
View Related
Feb 25, 2004
Hi
The system tables syslocks,syslockinfo give information on locked resources, spid, object_id,lock mode, lock status etc.
How will i get object_name
for example if a table is being locked above sys tables give object id, how can i get the actual table name ( object_name)
Please let me know
Best Regards
THNQdigital
View 4 Replies
View Related
Oct 3, 2007
I have a Sql Express Database that I want to access from a remote tablet application. When the remote user accesses a record (which is a form + ink), I can't have anyone else accessing that record and drawing ink on the record's form, unitl the first user is done. How do I prevent access to that record in a user friendly way until the remote user is done and has updated his changes to the database?
thanks!
View 6 Replies
View Related
Feb 12, 2008
Hi there,
I just wrote an article on how to Lock Down SQL Server 2005 that might interest people here. It has step-by-step instructions and SQL scripts for server and database hardening. A lot of concepts are explained along the way.
As an experiment I also have a world-accessible SQL2005 shell to let people experiment with a hardened database and see if they can do privilege escalation. A lot of the standard tricks have been tried so far and the server has held up without any problems. I wonder if that will last
Anyhow, the article is here: http://duartes.org/gustavo/articles/Lock-Down-SQL-Server-2005.aspx. I appreciate all feedback.
cheers,
Gustavo
View 3 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
Mar 9, 2015
I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?
View 9 Replies
View Related
Apr 9, 2003
I need to lock a table in SQL Server. The reason is that I need to insert a value into an identity column (rather than just taking the next sequence number). I have a program that:
1. Sets identity insert on
2. Inserts into the table
3. Sets identity insert off
The problem is that I believe I should lock the table before I turn identity insert on (otherwise, other transactions will fail). I need to ensure that no other operations will take place on the table while I am doing all three of these steps.
I believe I can set the isolation level, but I cannot find any way to establish the lock without executing the update statement itself - which is a nanosecond too late to prevent another user from getting an error.
View 6 Replies
View Related
May 17, 2006
I have a 3rd party app which gets the error below on my sql 2000 box. I can't change the app or the db, so I am going to need to tweak sql to make the plane fly. The box is a new dell quad attached to a SAN. Runs on Win 2003 with 16 gig of RAM. 450 concurrent users. Anyone think more memory would help? I have the locks set to default. Are locks more of a memory killer, or CPU?
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.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
View 3 Replies
View Related
Mar 22, 2007
We have an old VB6 app using a SLQ Server 2005 database.
This database is also used by a web service.
When accessed just by the VB6 or WebService everything runs smoothly.
The problem occur`s when both access the database in parallel.
Our web service seem`s to encounter some very long delay for queries running just fine when is alone.
Now my knowledge of VB6 is very limited (and it will stay that way :) i`ve checked the code for access part and some parts look obscur.
so here is the part where the queries gets executed
Set m_adoRecordSet = New ADODB.Recordset
With m_adoRecordSet
.CursorLocation = iCursorLocation
.CursorType = iCursorType
.LockType = iLockType
Set .ActiveConnection = objConnection
.Open m_strSQL
End With
i looked everywhere in the code and iCursorLocation is mainly always SQLClient. i think only one time is SQLServer.
iCursorType is always SQLForwardOnly and iLockType is always SQLReadOnly.
now the curious part (for me) is that the connection (objConnection) is opened at the beginning of the app and never closed? Is that normal in VB6?
Would the cursor being sometimes on the server be the problem?
If someone could help it would be very appreciated.
If you need more details just tell me.
thanks
mateo
View 1 Replies
View Related
May 16, 2007
I have written an app that will allow you to send a query to Teradata, return the results into a Reader and then Bulk Copy that data into SQL Server 2005.
If the query results in a large dataset (ie 20,000,000 rows) is processed then while that data is being bulk copied into SQL Server, using the SQLBulkCopy class, then it prevents users on other computers from logging into SQL Server Management Studio.
Those that are already logged in are shut down also. Everything appears fine to the users but queries do not finish running.
Everything immediatly starts working as normal when either my program finishes or I shut down my program.
Is there any type of property to the SQLBulkCopy class or any other function that will prevent Management Studio from locking up?
Thanks
Robert
View 2 Replies
View Related
Feb 17, 2006
Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.
begin tran update table1 set column1 = 100 if @ERROR = 0 begin declare @stat int set @stat = (select stat from table2 where employeeid = 10) insert into table3 (col1, col2) values (@stat , 325) if @@ERROR = 0 commit tran else rollback tran end else roll back tran
View 2 Replies
View Related
