Tag Info

New answers tagged

12

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 ...


0

The whole point of snapshot is to provide a point in time copy of the articles involved in replication. It does it when generating the snapshot files from the publisher by locking the tables until the snapshot is finished for that particular article. You should plan to reinitialize during less activity time of your server. Is there anyway to do this ...


1

From the MySQL Documentation The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE: By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same ...


0

Thanks to jynus for advising on working with general log, the problem has been solved! As in most of the cases, the error was quite simple, here is the description: On certain occasions, a function was invoked, that performed the following: Lock several tables in the DB Do its queries Send a message to chat with the results Unlock tables The message to ...


3

Would a different kind of column be faster? For example an integer No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway. Is there some way to not lock the column? It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating. ...


1

In the referenced link How to force the use of row locks? Paul White demonstrates that index reorganization cannot happen if that index has page locks disabled. You can, of course, reset Page locking back on before doing a reorganization and then turn it off again afterward: -- Allow page locks on Index ALTER INDEX indexname ON tablename SET ...


5

Do I need to make the "unlock" SERIALIZABLE as well? Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather, serializable guarantees transactions will have the same persistent effects on the database as if they had executed sequentially, in some undefined order ...


0

The documentation says the following: ROW EXCLUSIVE Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any ...



Top 50 recent answers are included