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?



ADVERTISEMENT

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 then I 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







Copyrights 2005-15 www.BigResource.com, All rights reserved