New answers tagged locking
0
5 million rows is a lot, theoretically it would cause shared lock escalation for TableA to table-level lock in any case (explicit or implicit transaction)
But you can find this out experimentally, you just need to setup Extended Events session and monitor for "lock_escalation" event
Then run your query in explicit and implicit transaction and check out XE ...
1
In this specific example, because it includes a SELECT...INTO statement, I think it's fair to say that yes, there is a difference in the locking behavior.
Source Table (and metadata tables)
The SELECT...INTO statement is not an "atomic statement," it runs in two parts: creating the new table, and then performing the SELECT and INSERT portion of the query.
...
0
Assuming that it works like PostgreSQL in this case:
This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level.
That is nothing to worry about, just repeat the transaction.
2
Delete several billion records old records (roughly 600GB)
In this case it's may be more convenient to copy the rows that should remain rather than delete. Note that all 600Gb will move to your log file surely.
By default, a DELETE statement always acquires an exclusive (X) lock
on the table it modifies, and holds that lock until the transaction
...
1
This is the answer IBM provided for the PMR, it makes sense so I'll add it as an answer:
Using isolation level RS (Db2's terminology for what java indicates as TRANSACTION_REPEATABLE_READ), we are allowed to find more qualifying rows on repeating the same query in the same transaction. The T3 scan could be halfway through table and some or all of the ...
0
In synchronous mode, the Primary has to wait for the Secondary to harden the transaction before the transaction can be committed on the primary. Latency, either from hardware or traffic, can cause the log files on the primary to fill which can put additional load on the CPU and disks to clear the log file(s) during a log backup.
Differences between synch ...
2
In very simple terms:
In asynchronous mode, if the replica can't keep up with its primary then the log send queue will just increase indefinitely until the load is reduced.
In perfmon:
SQLServer:Database Replica Log Send Queue
Bascially the primary will keep humming along but the replicas will just end up further and further behind.
In synchronous mode,...
0
yes , in Mysql SELECT itself is considered a transaction.
so if one transaction is on a table , you can not get meta data lock for alter.
1
To start with, sys.tables is a system catalog view, not a table. And DMVs / DMFs (especially those that report stats) often query memory, which is why they get cleared out when you restart or execute DBCC FREESYSTEMCACHE(N'ALL');.
Still, since sys.tables does eventually lead to actual system tables, I figured I should test this, especially because I have ...
4
It depends on the transaction isolation level.
In read committed it will release locks.
In "Repeatable read" and "Serializable" it will hold locks.
You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL
4
My understanding is that this type of lock is during the process of
identifying the range where the newly inserted key should be placed
(I'm assuming that's what 'test the range' means). After this happens
the lock is released, the new key gets inserted and an X lock is
placed on it.
RangeI-N lock is requested to test that insertion into key range ...
Top 50 recent answers are included
