May 30, 2012 at 7:05 pm
We are starting to get quite a few DB locks. I believe the Lock parm for our DB is RC. If user '1' selects from a table, will user '2' experience a lock if he selects from the same table? The values the users are selecting from columns in the index are different.
May 30, 2012 at 8:00 pm
Shared locks are compatible with other shared locks, so you shouldn't be experiencing any blocking from your explained scenario. It is also worth noting that read committed transaction isolation level only holds shared locks for the duration of the read, not necessarily the whole transaction.
What are you seeing?? Utilize the sys.dm_tran_locks DMV to view current locks and their corresponding information.
Don't confuse locking with blocking. The former doesn't always cause the latter.
May 30, 2012 at 9:52 pm
I'm not a dba and I don't think I can view the locks but our consultant said we experienced a lock. I am just trying to figure out why the following would cause a lock - the dba sent this:
user_a was executing "SELECT * FROM TABLE_A WHERE master_id = '3332' AND scale_id = 'NORTH';SELECT * FROM TABLE_B WHERE master_id = '3332' AND scale_id = 'NORTH' AND field = 'NORTH';
and user_b was executing SELECT * FROM TABLE_A WHERE master_id = '3340' AND scale_id = 'SOUTH';SELECT * FROM TABLE_B WHERE master_id = '3340' AND scale_id = 'SOUTH' AND field = 'SOUTH';
user_b was about to do an insert but user_a had the TABLE_A table locked
May 31, 2012 at 5:06 am
Yeah, that's pretty normal. SQL Server ensures that each of the transactions within it maintains ACID properties (Atomic, Consistant, Isolated, Durable) through the use of locking. This prevents data from being changed as it's read, stuff like that. So yes, if I'm reading data you can't update it at the same time.
Now, there is a way around that to a bit. You can look at using snapshot isolation. This allows for versions of data so that reads don't interfere with updates & deletes and vice versa.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 31, 2012 at 7:43 am
I guess the main problem is that it locks up the system for a long time. Within our application, the system will hang for about 10 minutes when this occurs.
May 31, 2012 at 8:15 am
Rog Saber (5/31/2012)
I guess the main problem is that it locks up the system for a long time. Within our application, the system will hang for about 10 minutes when this occurs.
Then I would focus on tuning those queries so that they perform faster. A simple set of lookups as you've illustrated should be sub-second, not 10 minutes. Either those queries are extremely heinous, or something else is up within the process or your system.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply