New answers tagged

1

Locks are taken to preserve transaction isolation as described on the Transaction Basics doc. If the release of the lock doesn't hurt the ACID properties, there's no reason for SQL Server to keep a resource locked and, in fact, the sooner the lock is released the better for good performance. If you see the Lock Modes, you'll find that the query from your ...


0

For what I know, sql releases the lock when it does not need the resource anymore, i.e after commit. It follows the Two-Phase Locking. I would recommend trying to start your union query and then start an update on the non-archive table to see if the update goes after 5 seconds or after 1 minute.


0

Erwin's answer is state of the art. But for completeness sake I'd like to suggest more options. Pessimistic Lock First one is typical - using Pessimistic Lock on the coupon row, it must be taken in the beginning: select * from coupon where id=? and used=false for update insert into log(...) The 2nd transaction will wait until the 1st one is finished. If 1st ...


-2

I'm only writing this as an answer due to the severity of your actions that I reprieve. I was redesigning legacy database and have merged lots of tables into just four This is a major Red Flag to me. It screams "EAV". Even if it is not, do the following: Make sure you benchmark your new design with the application Make sure the application is ...


3

these tables have foreign keys on a highly referenced table and lock is required by drop If FK constraints point to a table to be deleted, add CASCADE to also drop any such FK constraint (not the referencing tables). The manual: (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not ...


0

I have also searched answer for this question and we use similar to your option 1. We create table CREATE TABLE "EVENT" ( "GUID" VARCHAR2(40) NOT NULL ENABLE, .... "BODY" CLOB NOT NULL ENABLE, "DO_SEND" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, "MOMENT_CREATE" TIMESTAMP (6) DEFAULT ...


Top 50 recent answers are included