Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Remove Lock?


From Sql-server enterprise manager : Management-Current Activity-Locks/Process ID, I found several locks, which preventing some of application working property.

So, I want to remove locks manually.
How Can I do this?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To STOP Row Lock ==> To TABLE Lock- SQLSERVER AUTOMAGICALLY
Hi ALL

Environment
H/W-Dual PIII, 1GB RAM, 25GB Hard drives
S/W-NT 4.0 SP5, sqlserver 7.0 sp1, MTS, VB COM objs, ADO 2.1

This one is giving me some extra gray......
We have an order entry system, where we want to "create" invoices from about
20000- 25000 orders
at a time we WANT this to be a single transaction speed is not the issue
concurrency IS we DO not want
SQL to escalate the lock to table, but as we watch with sp_lock, row lock
occur for the first 2-3000 rows then it becomes a table lock.
I have read that if a query involves a table scan then it will escalate to a
table lock , that is why we have the hint to use the PK_OrderMain index, I thought sp_indexoption was going to help but all you can do is stop row/page locks
Does any one know how to stop sqlserver from automagically putting table
locks on ??????

our VB code looks some thing like this

sql = SELECT blah FROM OrderMain WITH
(ROWLOCK,UPDLOCK,INDEX=PK_OrderMain) WHERE OrderMain.OrderID in
(SELECT OrderMain.OrderID FROM OrderMain WITH (READCOMMITTED) WHERE
OrderMain.OrderStatus='A'

rs.open sql .....
while not rs.eof
...process orders
loop

if not errorflag
rs.updatebatch
end if

View Replies !   View Related
Row Lock Versus Page Lock In SQL 2000.
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 Replies !   View Related
Error: A Deadlock Was Detected While Trying To Lock Variable &&"x&&" For Read Access. A Lock Could Not Be Acquired After 16 Attempts
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 Replies !   View Related
How Do I Clean Up The SQL Server (ctp) From ADD/REMOVE Program Without The Change/remove Button
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.

Thanks

deebeez1

View Replies !   View Related
ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.
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 Replies !   View Related
A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List
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 Replies !   View Related
Unable To Remove SQL Instance In Add/Remove Program
I need help,

I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and thenI tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system?

I appreciate your help, Thanks

IS Support

View Replies !   View Related
SQL Server Management Studio: Can Not Remove AdventureWorksDB In &&"Add Or Remove Programs&&" Of Control Panel
Hi all,

I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB     Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5.      |OK|   and (2) Add or Remove Programs   Fatal Error during installation (after I clicked the |OK| button).   Please help and tell me how I can solve this problem.

Thanks in advance,

Scott  Chang 

View Replies !   View Related
How To Lock A Table So Others Cannot Lock It
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 Replies !   View Related
Lock Row.
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 Replies !   View Related
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.

View Replies !   View Related
CPU Lock Up
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 Replies !   View Related
SP Lock!
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 Replies !   View Related
With (no Lock)
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 Replies !   View Related
DB Lock-up??!!
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 Replies !   View Related
How To Lock Out Everyone But Me?
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 Replies !   View Related
No Lock
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 Replies !   View Related
How Can I Lock A Record
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 Replies !   View Related
LOCK DB WHEN INSERTING
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 Replies !   View Related
Transaction Lock
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 Replies !   View Related
How To Lock A Table Or Row In Sql?
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 Replies !   View Related
Lock SQL Table Using Asp.net
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 Replies !   View Related
Table Lock
Hi,If I run an insert statement from the query analyzer and then try toopen the table from enterprise manager then it takes long time to openthe table. But this problem dissapears when i put the statement insideBegin/End Transaction statement.Any idea why this is happening?Thank in advance.Taw.

View Replies !   View Related
100,000 Lock Requests/sec
I'm monitoring one of our servers, and on the whole it is performingwell. However, I'm puzzled by the number of LockRequests/sec thatPerfmon is recording. We frequently see values exceeding 50,000 andthe current peak is 533,616 (the average, as I type this, is 35,102).There are only 40 users on the system.sp_lock shows nothing like this number of locks; it shows somethingof the order of 30 locks maximum for each execution, which seems farmore reasonable.SQL Profiler seems to back up the Perfmon values; it records hundredsof "Lock Acquired" events per second during these peak periods.For example:NO. OFLOCKS OBJECT STARTTIME===== ====== ======================678User_T2005-04-15 09:03:22.863931User_T2005-04-15 09:03:22.877924User_T2005-04-15 09:03:22.89316EnquiryUser_T2005-04-15 09:03:22.893961User_T2005-04-15 09:03:22.910820User_T2005-04-15 09:03:22.9234NULL2005-04-15 09:03:22.923828User_T2005-04-15 09:03:22.9404NULL2005-04-15 09:03:22.940734User_T2005-04-15 09:03:22.957Can anyone think why such a comparatively small system should generatethese numbers of locks? Why does sp_lock NOT show the same level oflocking?

View Replies !   View Related
Table Lock
Which lock type or isolation level should I use to be sure that no onewill read or write or do anything with the table I'm using?Code block should look something like this:lock tableread value from tablechange value to new_valueupdate table set value = new_valuerelease lockWhile I'm changing the value absolutly no one should be able to readfrom the table.

View Replies !   View Related
Eternal Lock?
Hi,does sb recognize (aspects of) the following problem? Or better, know asolution or direction to search?At work I've inherited a series of delphi applications that access a commondatabase using SQL Server 2000 (sp3, sp4 update in preparation due to thisproblem). Applications run on one server, db on the second. Both are dualxeon 2.8 or 3 GHz with 2 GB ram. The apps use about one GB (800MB) memory,the db too (is configured to use more, 1.8GB, but doesn't.) The db is alsoreplicated to a third machine.The problem is that sometimes, after a cascade of query timeouts (recordedby the apps in the eventlog, cause is the commandtime set on allcomponents), the whole applications seems to stop responding. Restarting theapps doesn't solve the problem, rebooting the application server does, whichleads me to believe the problem is in MDAC on the app server? The app serverhas an own unused sql server instance (used in migrations) btw.The problems occur during busier times, but nothing spectacular (up toten-thousand of queries per hour maybe).The problem sometimes goes away after a few minutes in about half of thecases, but if not, it seems perpetual till reboot (at least 13 hours).Another notable point is that not all queries time out, most writes (whichappend a row or change a row) seem to go ok, same with selects that get arecord for a primary key value, and pure read selects flagged with NOLOCK.The queries that go wrong all get lists that touch central tables (eitherdirectly or via joins).The behaviour is consistent with an external row/page lock somewhere thatdoesn't go away.Database layout is fairly uninteresting. A db or 3 (one read-only), thelarger one having say 30 tables,cardinality of the tables is not that much of a problem. Tens of thousandsof rows max, except a logging table with maybe 300000 tuples. (which is onlytraversed for mgmnt info, and not during busy hours) No binary or otherdisproportionally large fields, Most db access done based on primary/foreignkeys.Other details:- Replication overhead can be considered low (we are talking aboutthousand(s) mutationsper day, nothing significant.- commandtimeout on all db components is set (to 30s)- all cursors are clientside, except the component used for getting lists,that haslocation=cluseserver; cursortype=ctopenforwardonly;cachesize=250; locktype=readonly- the apps are not threaded.- D6 patched with all three patchesThnks in advance

View Replies !   View Related
What Is A MISCELLANEOUS Lock?
My server is running an important stored proc.

The system is reporting very low CPU and disk use, and there is no important network activity (all relevant data is local). In Enterprise Manager, my process is listed as sleeping with a "wait type" of "miscellaneous". What could this possibly mean? How can I diagnose?

View Replies !   View Related
Dead Lock
Hello !!!

I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.

Note: When I execute each transaction independient I have no problems.

Is there any possibility to lock table A completly for the first transaction and release It for second one ??

Could you give me any suggestion of what to do step by step ?

Thanks !!!

View Replies !   View Related
I Want To Lock One Record
Please help! I have a VB application used by multiple users. MS SQL 2000 backend.

When a user opens a record for editing, it want to mark the record as locked so that if other users try to open this record, it will not be available.

Currently, I'm using a programmatic method that basically sets a field 'lock" to 1 if the record is in use and 0 if not in use. But this has the side affact of locking the record if the user reboots their workstation. In addition, I don't believe that this is the best method and approach to achieve my goal.

Thanks very much for your help in advance. All ideas are welcome.

crownlog

View Replies !   View Related
Deadlock On TAB Lock
I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I got this TAB lock situation instead as following:


2006-01-18 09:51:37.87 spid4 ----------------------------------
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15

Deadlock encountered .... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 ----------------------------------

How can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Any kind of help will be appreciate.

Hanson

View Replies !   View Related
SQL Range Lock?
I have run into an interesting issue that I have yet to resolve.

I use SQL 2000 server as a backend and Access 2003 as a front end. We manufacture products and keep track of the serial numbers is the database. When I create new records and attempt to autogenerate new serial numbers (using VBA), occasionally SQL will not accept them. I get a "ODBC - Update on a linked table '<table name'> failed".

I should point out that the serial numbers are not the primary key. I also have an autoID column as well.

The weird part is that it only locks out a serial number range. For example I can enter any new serial number from 5001234~5999999 with no errors but new serial numbers 1610098~1619999 will generate that error.

The form to enter the data in access is simple and there are no custom record locks being used. This problem appears to come and go every few months and has recently become a bigger problem as it is occurring more frequently.

I believe SQL must have initialted a Range Lock but I dont know how. Does anyone know anything about this?

View Replies !   View Related
Table Lock...
Hello Friends,
I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...




Thanks in advance........
Rahul Jha

View Replies !   View Related
Record Lock
hi,

using VB 6.0, sql server 7.0, ADO 2.5

i will tell my requirement. i have a tran. window there i will select a chart number of a patient and do some changes and will go to next window for doing some other modification for the same patient, and again will go to other window. by this time don't allow other users to access the same patient chart number. should get a message like record locked by some other user.

please help me to solve this issue

prince

View Replies !   View Related
Lock Database
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

View Replies !   View Related
Record Lock
i have an app that insert multiple rows into a table in sql using insert statments. i use a begin trans and commit trans.

here is the setup i have a table that contains 100 rows. i insert another 25 rows. in another app i try to edit a record in that table (a record from the first block of 100) i can't edit the record because my first app has not done its commit trans yet. i don't understand why i can't edit a record that was already in the table. i'm not trying to edit one of my newly inserted records.

please advise,
Thanks
Thomas

View Replies !   View Related
Sp_sproc_columns And Lock?!?!
We've noticed quite a few locks lately that are causing us quite a few problems and when we run sp_lock and then dbcc inputbuffer on the spid causing the lock it almost always shows sp_sproc_columns.

Can someone explain to me what's going on, and/or if there is any way around this as far as causing locks?

One of our nightly maintenance jobs does an SP_Recompile for each table in our database. We're curious if this is causing problems when each Stored Procedure is re-compiled the first time that its run.

Many Thanks,
Tim

View Replies !   View Related
Database Lock
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 ?

View Replies !   View Related
Page Lock
I have come a cross a table DDL with (LOCK ALLPAGES). I am aware that page locks is good in order to reduce the contention and improve user concurrency. Can anyone tell me if it is a good idea to include that in a table DDL? Or what are the advantages and disadvantages? I look forward to hearing from you.

Regards,

View Replies !   View Related
Key Range Lock And ADO
I see lots of Key Range Locks (RangeS-S) in the server and
I read that you can get this kind of locking only if you
have transaction isolation level set to REPEATABLE READ or
SERIALIZABLE.

We are using a COM+ component with DTC to connect to the
database. The ADO (2.6) connection object does not
explicitly set ISOLATIONLEVEL propery, so it should be
using adXactChaos isolation by default. But by the
definition of this isolation level it does not look like
it is serializable or repeatable read.

What can be cause this kind of lock?. We see lots of
blocking and deadlocking of this.

For now I am thinking of setting the isolation level to
adXactReadCommitted in ADO explicitly, since it requires
all the rebuilds, I want to be sure before I propose this.

Any pointer is highly appreciated.

Thanks

.

View Replies !   View Related
Lock Timeout?
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 Replies !   View Related
Lock Help On Sql 2000
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 Replies !   View Related
Lock Setting
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 Replies !   View Related
Support Row Lock??
Do SQL 7 support row lock?

View Replies !   View Related
Two Lock Questions
I have two questions regarding locking in SQL 2000.

1) If I set a lock whithin a transaction and the transaction fails/rollsback.
Does the lock get released as well?

2) If a connection is lost while in a transaction and a lock was set during
the transaction. Does the lock get released?


Thanks

View Replies !   View Related
Lock Quesiont
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 Replies !   View Related
Permissions Lock
I have setup a role and when I click on the permissions button an hour glass appears and then the mouse arrow returns. The problem is that it does not show me the permissions for this role and the system becomes locked. I can click anywhere on the screen and there is no response.

View Replies !   View Related
Insert Lock
I'm inserting on average 2000 rows, sometimes even more. Sql starts to create exclusive locks until my insert process is deadlocking. What is the best way to do a big select - insert, so that I can keep locking to a minimum?

View Replies !   View Related
How To Lock Record In SQL 7.0?
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 Replies !   View Related
Lock Blocks
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 Replies !   View Related
FIL Lock On Object
We have a process that connects to SQL server 7 through an application that uses an ODBC connection. All this connection does is update some table structures to one of the databases. What has been happening is twice a day this connection will cause a FIL lock on a certain table in the database. Can any one give some insight as to exactly what a FIL lock is? I know it's a 'file lock', but can not figure out what is causing it or what exactly a FIL lock infer. Most locks I have seen are a DB, IDX, TAB, PG. Any thoughts would be appreciated.

View Replies !   View Related
Lock Option
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 Replies !   View Related
Lock Escalation
Hi,
I renamed a table from Cust(Original) to Cust_rename and made another table by same name(Cust) - reason to clean up some data from Cust_Rename table - and to avoid deadlocks which may be caused due to Delete statement.
I also run a trace to capture the activities on production box.
while I did this - I saw no blocking...but trace showed that 'Lock Escalation' occured on Cust table....How can this happen when I am performing 'delete' operation on Cust_Rename table....and I noticed that no rows where added/inserted in Cust table......People complain that they are getting blocking messages....
any insight appreciated....
TIA..

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved