The tag has no usage guidance.

learn more… | top users | synonyms

21
votes
3answers
7k views

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, ...
18
votes
2answers
16k views

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question. ...
8
votes
3answers
839 views

“When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”?

The MSDN online article "Snapshot Isolation in SQL Server" states: "An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, ...
8
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) ...
8
votes
2answers
770 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
8
votes
2answers
586 views

SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table. I have already changed all the required parameters and made them into row only locking. I've also changed the ...
7
votes
3answers
4k views

SQL Server - what isolation level for non-blocking select statements?

I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
7
votes
4answers
4k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
6
votes
2answers
242 views

Where does InnoDB store transaction data before committing it?

I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology. I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not ...
6
votes
1answer
3k views

How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is: Transaction ...
6
votes
1answer
2k views

SELECT INTO OUTFILE vs INSERT INTO … SELECT

During my cursory research I have not been able to find a definitive answer on the benefits that SELECT INTO OUTFILE provides over INSERT INTO ... SELECT. Upon reading the docs related to INSERT INTO ...
6
votes
1answer
388 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
5
votes
1answer
663 views

Why are U locks required when using Read Committed Snapshot Isolation

I consider myself a beginner at Sql Server locking. My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From ...
5
votes
2answers
641 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
5
votes
1answer
236 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
4
votes
1answer
5k views

Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the majority of my general SQL queries, mainly because this was drilled in to me when originally learning the language. From my ...
4
votes
1answer
6k views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
3answers
670 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
4
votes
2answers
620 views

Schema blocking in READ_COMMITTED_SNAPSHOT

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues. Background: Assume an online ordering system (ecommerce). ...
4
votes
1answer
201 views

InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?

I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around? For examples: ...
4
votes
2answers
876 views

Isolation Level for a Data Warehouse

We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our ...
4
votes
1answer
51 views

Does SQL92's REPEATABLE-READ preclude Write Skew (A5B)?

REPEATABLE READ In SQL92's definition, REPEATABLE READ is defined by serveral conditions. P1 ("Dirty read"): 1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then ...
4
votes
1answer
161 views

Why do row-versioning isolations use a linked list of versions of record? [closed]

Trying to better understand row-versioning based transaction isolations in SQL Server... According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation": ...
3
votes
2answers
414 views

Why is WITH NOLOCK running as Serializable

I ran the following query: SELECT session_id,CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 ...
3
votes
1answer
96 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
3
votes
1answer
2k views

Implementing application locks within SQL Server (Distributed Locking Pattern)

In my application I have to perform a distributed locking pattern. Because we already have an instance of SQL Server to use, we decided that it would be easiest to implement the locking at SQL layer ...
3
votes
1answer
3k views

Why can't I change the isolation levels of my connection in MySQL?

I am trying to experiment a bit with the isolation level of MySQL in a test environment. I do the following: mysql> set @@session.tx_isolation='READ-UNCOMMITED'; ERROR 1231 (42000): Variable ...
3
votes
1answer
1k views

Execution of sequential delete and uncommitted read

We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is: Could not continue scan with NOLOCK due to data ...
3
votes
1answer
170 views

Is REPEATABLE READ the right choice for validation before modifying data?

In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end. When a new task is created ...
3
votes
1answer
149 views

Why is READ COMMITTED a common default transaction isolation level?

I am wondering if anyone knows the history of why READ COMMITTED is the default transaction isolation level for PostgreSQL, SQL Server, Oracle, Vertica, DB2, Informix, and Sybase. MySQL uses default ...
3
votes
0answers
110 views

Phantom vs. Non-repeatable read [closed]

In Martin Fowler's book 'Patterns of Enterprise Application Architecture' on page 74, the authors say It's easier for databases to spot unrepeatable reads than phantoms, so the repeatable read ...
2
votes
4answers
491 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
2
votes
2answers
185 views

How do I set the isolation level for SELECT statement?

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was ...
2
votes
3answers
328 views

Transaction Isolation level different threads massive number of Writes

I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data. I also need the writing to be superfast. ...
2
votes
2answers
2k views

Read Committed Isolation Level

Quote from docs: Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
2
votes
2answers
920 views

What effect does serializable isolation level have on DDL-statements?

I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works ...
2
votes
1answer
493 views

Stored procedure with dynamic sql. Is it isolation safe by default?

I would appreciate confirmation on the answer I found digging through T-SQL's documentation. I have a stored procedure that references a view twice. Once in the procedure and then a second time when ...
2
votes
1answer
56 views

set serializable isolation for update query postgresql --9.4

CREATE TABLE xtable(xtableid integer, xcolumn integer) CREATE TABLE ytable(ytableid integer, ycolumn integer) CREATE OR REPLACE FUNCTION foofunction(integer, integer) RETURNS void AS $$ BEGIN SET ...
2
votes
1answer
426 views

Exclusively access a table and prevent other queries, even read queries

I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application: Get unprocessed messages (using the IsProcessing flag) Set their IsProcessing to true (in RAM) ...
2
votes
1answer
164 views

What is the standard isolation level for a db used for money data?

When using a database server for monetary/financial data, I can only assume that using transactions is mandatory. But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE ...
2
votes
1answer
189 views

MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

In MySQL documentation it's written: If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. Why ...
2
votes
1answer
1k views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
2
votes
1answer
224 views

Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is ...
2
votes
0answers
943 views

SQL Server - Same query, same database, different results [closed]

I have a database in SQL Server 2008 with 2 tables. I'm running the following query on Management Studio: select M.FD_BROWSER_SHORT_NAME AS X, Count(1) AS Y from TB_EVENTS E INNER JOIN ...
1
vote
3answers
1k views

SELECT lock only for inserts [closed]

I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the ...
1
vote
1answer
50 views

Updlock hint in stored procedure

If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time? CREATE PROCEDURE ...
1
vote
1answer
82 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
1
vote
1answer
130 views

How, when and why does implicit changing of transaction isolation level happen?

RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server. This is, as I understand, the context of ...
1
vote
1answer
496 views

How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database? [closed]

I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore. In fact, I would like to force the ...
1
vote
1answer
333 views

How to insert and or update a variable number of records and prevent concurrency issues

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be ...