Hot answers tagged

36

DB locks can exist on rows, pages or whole tables or indexes. When a transaction is in progress, the locks held by the transaction take up resources. Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover ...


35

Use this one command: RENAME TABLE foo TO foo_old, foo_new To foo; It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.


29

It isn't always bad. Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all. The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values ...


22

If your report blocks updates that your DBA is right: you should absolutely not use NOLOCK. The very fact that there are conflicts is a clear indication that if you would use dirty reads you would get incorrect reports. In my opinion, there are always better alternatives than NOLOCK: Are your production tables read only in effect and never get modified? ...


19

"It depends" If you add a column that does require adding data to the rows, it can be quite quick. For example, adding an int or char requires physical row movements. Adding a nullable varchar with no default shouldn't (unless the NULL bitmap needs to expand) You need to try it on a restored copy of production to get an estimate Creating a new table, ...


18

Analogies Sometimes it helps to use analogies away from computers. Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball. The other ...


17

I believe this is by design, according to the description of the read-committed isolation level for PostgreSQL 9.2: UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time1. However, such a target row ...


17

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction? Yes. The allocation of identity values is independent of the containing user transaction....


16

You pick your battles and battles like this can't be easily won. We have a system where every DML is hinted with the ROWLOCK hint (irrespective of modifying one row or several thousand rows). I showed several examples why it really hurts performance but as the system is already working, there is resistance to change. Note that I convinced them enough NOT to ...


16

Extending Mark's answer... When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released. Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens ...


15

It's a method for reducing system overhead, by converting many fine grained locks to fewer coarse grained ones. More detailed information can be found here and here. For example, if you have many (usually hundreds or more) locks on specific rows in a table, once you exceed your maximum allowed number of locks, these might be exchanged for a lock on the ...


15

Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate. The important point here is that under the SQL standard, ...


15

I would like to understand whether the following, very simple select statement would take any locks It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads. SQL Server can avoid taking shared row-level locks when there is no danger of reading ...


15

You best bet is to use an explicit containing transaction and acquire a custom exclusive lock to protect the whole operation (SELECT and CREATE TABLE) using sp_getapplock. System objects do not honor isolation level requests and use locks in the same way as user tables, by design. The race condition in the original code is that multiple threads can conclude ...


15

The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics: Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce ...


15

If client takes long time to receive data and in turn send acknowledgement to SQL Server that it has received the data SQL Server has to wait, due to this wait SQL Server will not release the locks held by the query unless acknowledgement is received from client. This is not accurate, it is dependent on the isolation level. At the default READ ...


14

If the column is NULLable, the impact should be negligible. If the column cannot be NULL and the value must be set, then it can be quite different. What I would do in this case is, instead of adding a not null and default constraint in one shot, effectively adding data to every row: add the column as NULLable - should be quick in most cases update the ...


13

What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for ...


13

You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it. Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column. You can find which session has ...


13

I think this does what you need. USE 'yourDB' GO SELECT OBJECT_NAME(p.[object_id]) BlockedObject FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id ...


13

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses ...


12

Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized because page level locking is disabled." The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not ...


12

What is HOBT lock? A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index. Why would I still get a S lock? This happens on heaps. Example SET NOCOUNT ON; DECLARE @Query nvarchar(max) = N'DECLARE @C INT; SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options'; /*Run once so ...


11

It's been discussed on SO before: http://stackoverflow.com/questions/2141104/using-nolock-hint-in-ef4/2141802#2141802 http://stackoverflow.com/questions/1682240/what-can-happen-as-a-result-of-using-nolock-on-every-select-in-sql-sever Define very busy. We have high volumes (50k new rows per second, large aggregates etc) and don't see the need to get dodgy ...


11

You can do this with DBMS_LOCK and an exclusive lock. See the following procedure: CREATE OR REPLACE PROCEDURE myproc IS lockhandle VARCHAR2(128); retcode NUMBER; BEGIN DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle); retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode); IF retcode<>0 THEN ...


11

Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition values. USE yourdatabase; GO SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename'); Please look at the documentation for sys....


11

when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly? Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would ...


11

The default READ COMMITTED transaction isolation level guarantees that your transaction will not read uncommitted data. It does not guarantee that any data you read will remain the same if you read it again (repeatable reads) or that new data will not appear (phantoms). These same considerations apply to multiple data accesses within the same statement. ...


11

My recommendation would be to do a best effort try/catch. Handle the duplicate case explicitly, as appropriate, eg. ignore it... The real question: why is DDL running on-demand, from multiple xacts? Normally upgrade and migration are a serious matter, handled in dedicated time windows... You do not want your migration (code-first?) to kick in unexpectedly, ...


10

I'm answering this with hesitation as there isn't enough information in your description of the problem to be 100% sure this is the best advice. "Hangs or throws an exception" suggests the source of the issue isn't properly understood, so proceed with caution. The simplest solution to this is probably SET XACT_ABORT ON. XACT_ABORT determines whether SQL ...



Only top voted, non community-wiki answers of a minimum length are eligible