Remove Locking from particular row

Question

Post reply

Remove Locking from particular row

seenu_61281

Old Hand

Points: 335
More actions
June 12, 2008 at 4:42 am

#121957

i have a table in my database(sql 2005 express) which is used by more than 10 member
Problem.
-------
when i try to update one row..its not getting updated or deleted
but in other rows i can update and delete
why....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'its not getting updated or deleted'?

You get an error?

The update waits forever?

The update succeeds but the row isn't changed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

One of the cylinders on my car is misfiring... I can't understand why the mechanic blew me off when I told him he couldn't look under the hood because I left the car at home. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

seenu_61281 Old Hand Points: 335 More actions · Answer 3

i directly changed the data using enterprise manager.i waits for sometime and displaying timeout expired error.

error

------

first time i got lock timeout expired....

next time i am getting timeout expired...(for that particular row only...)

table constraints

-----------------

i have one primary key on a id field and one unique key combined two fields..

one of the uniquekey field is refers from other table (foreignkey)

pls help me....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Some other process is holding an extended lock on part or all of the table.

You can use the DMV sys.dm_tran_locks to see what locks are held and by what processes.

Locks should not be held for long periods, so either you have some badly written code running longer than it should, or you have bad indexes, or both.

I would suggest you find the query that's holding the locks and investigate it to see why it's locking for so long.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

seenu_61281 Old Hand Points: 335 More actions · Answer 5

thanks madam..i will try this..

one more doubt..

i am using sql 2005 express.i need to insert data from a grid control in vb 6.0. The entries are being done by other system (some other place )which is connected with relaince connectivity...they will put 300 to 500 rows at a time..

the existing code is "conn.execute" in vb code (this is in loop)

its getting very slow...users are not happy with this

i tried with xml string insert ,stored procedure..i couldnt find any speed.

which is the fastest way to insert data into the table??? need help

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

seenu_61281 (6/13/2008)
thanks madam..i will try this..
one more doubt..
i am using sql 2005 express.i need to insert data from a grid control in vb 6.0. The entries are being done by other system (some other place )which is connected with relaince connectivity...they will put 300 to 500 rows at a time..
the existing code is "conn.execute" in vb code (this is in loop)
its getting very slow...users are not happy with this
i tried with xml string insert ,stored procedure..i couldnt find any speed.
which is the fastest way to insert data into the table??? need help

Funny you should ask...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)