January 14, 2009 at 5:42 am
When I Insert data in a table in SQL server, what type of lock is acquired ? Is it Table level lock or row level lock ?
I am getting database deadlock, when trying to run 2 stored procedures which are trying to update/insert in same table ? What could be the reason ?
January 14, 2009 at 6:33 am
Can you turn on TraceFlag 1204 and capture the deadlock graph that it will generate in the error log and post it? That's the best way to help.
The exact locks placed by an insert vary depending on the amount of data being inserted. It really depends on the specific query. That's why the deadlock graph will tell you what's going on.
By the way, a simple insert from two different sessions to the same table won't cause a deadlock. You'll get blocking for the extent of the transaction, but not a deadlock.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 8:04 am
If you're on SQL 2005, and I assume you are because this is the SQL 2005 forum, use traceflag 1222 rather than 1204. It produces a lot more output and it's a lot easier to interpret.
DBCC TRACEON (1222,-1)
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
January 14, 2009 at 8:39 am
GilaMonster (1/14/2009)
If you're on SQL 2005, and I assume you are because this is the SQL 2005 forum, use traceflag 1222 rather than 1204. It produces a lot more output and it's a lot easier to interpret.
DBCC TRACEON (1222,-1)
Sorry, I knew as I typed it I was saying the wrong thing. Thanks for the correction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2009 at 11:30 pm
anjali.vishwakarma (1/14/2009)
When I Insert data in a table in SQL server, what type of lock is acquired ? Is it Table level lock or row level lock ?I am getting database deadlock, when trying to run 2 stored procedures which are trying to update/insert in same table ? What could be the reason ?
U have to require row level lock with some range of columns..
January 16, 2009 at 5:52 am
Paresh Prajapati (1/15/2009)
U have to require row level lock with some range of columns..
Really? What happens if you're updating more than one row? What about if you were updating 1/2 the rows that fit on a page? What about if you where updating half the rows in a table?
It really depends on how much of the data in a structure is being affected. The system will make the most efficient lock determinations it can.
By the way, it doesn't even take 1/2 the rows in a page to get a page lock instead of a set of row locks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2009 at 6:58 am
Grant Fritchey (1/16/2009)
Paresh Prajapati (1/15/2009)
U have to require row level lock with some range of columns..Really? What happens if you're updating more than one row? What about if you were updating 1/2 the rows that fit on a page? What about if you where updating half the rows in a table?
It really depends on how much of the data in a structure is being affected. The system will make the most efficient lock determinations it can.
By the way, it doesn't even take 1/2 the rows in a page to get a page lock instead of a set of row locks.
But page lock is lock all other rows which is not required to be lock, so other process which has require those rows then it is not geninune..
January 16, 2009 at 7:03 am
Paresh Prajapati (1/16/2009)
But page lock is lock all other rows which is not required to be lock, so other process which has require those rows then it is not geninune..
Yes, but it can be more efficient for SQL Server to take a single page lock rather than 100's of row locks. It will take less time, less memory and less processing power. So while, on the surface, it appears that it's more costly, it actually causes less contention.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply