New answers tagged

2

My favourite way of achieving this is the OUTPUT clause. Here is an example: SET NOCOUNT ON; -- example setup for the queue table DECLARE @queue TABLE ( transaction_id int PRIMARY KEY, processed bit ); -- some sample data INSERT INTO @queue VALUES (1,0), (2,0), (3,0), (4,0), (5,0); Now the processing thread: -- processing ...


3

1) InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed) how busy your server is (purge lag) ...


1

Readers should not block readers, so if no one writes to the table I don't think using UR instead of CS will make much of a a difference. There is a small overhead I guess for applying and releasing the share lock, but my guess is that it will be negligible. I guess only way to find out for sure is to try and see if it affects throughput. I was unaware ...


1

By default, SQL Server writers block readers and vice versa if Read Committed Snapshot Isolation is not enabled. If the application is not written exclusively for SQL Server, it may cause lots of extra locks (SQL Server can also escalate locks) when database is used more or less extensively. Enabling Read Committed Snapshot Isolation solves this issue and ...



Top 50 recent answers are included