The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
0answers
18 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
2
votes
3answers
148 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. ...
1
vote
1answer
31 views

What are the considerations when using SERLIALIZABLE over READ COMMITTED isolation level in Oracle?

What are the considerations when using SERIALIZABLE isolation level over the default READ COMMITTED in an Oracle database? Given the concurrency system used within Oracle (using transaction logs to ...
0
votes
1answer
73 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
0
votes
1answer
26 views

Is reading a single record always an atomic operation?

Is reading a single record always an atomic operation? I mean, when e.g. a read (select) and write (update) query are simultaneously carried out on the same record, is it possible for the read ...
1
vote
1answer
38 views

Is it possible that records are changed while a read query is still executing?

Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records? We didn't take any ...
1
vote
1answer
94 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
98 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
1answer
185 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 ...
5
votes
1answer
331 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 ...
1
vote
0answers
43 views

Transactions using Isolation Level on databases with FileStream

We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting ...
0
votes
1answer
106 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 ...
2
votes
0answers
416 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 ...
0
votes
1answer
168 views

Best Isolation Level to avoid deadlocks in Sql Server [duplicate]

I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs. wich Isolation Level do you recomend for L1 ...
1
vote
1answer
78 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 ...
7
votes
3answers
258 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, ...
3
votes
1answer
104 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": ...
0
votes
0answers
119 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
2
votes
1answer
123 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 ...
0
votes
0answers
68 views

MySQL server vs client isolation levels

What is the difference between setting the transaction isolation level in the client configuration and the server configuration? If I set the client isolation level to be serializable while the ...
3
votes
1answer
953 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 ...
1
vote
1answer
178 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 ...
1
vote
3answers
370 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 ...
6
votes
1answer
959 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 ...
2
votes
2answers
645 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 ...
8
votes
2answers
5k 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. ...
1
vote
0answers
34 views

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

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. ...
4
votes
2answers
413 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. ...
3
votes
1answer
691 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 ...
0
votes
0answers
97 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
0
votes
1answer
843 views

Does TRUNCATE TABLE affects isolation level in Sql Server?

As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project.... As I have to make some ...
6
votes
2answers
397 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 ...
6
votes
4answers
2k 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 ...
5
votes
1answer
221 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. ...
4
votes
1answer
2k 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
2answers
546 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
2answers
380 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). ...
8
votes
1answer
490 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 ...
4
votes
3answers
453 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 ...
5
votes
1answer
201 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, ...
2
votes
2answers
775 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 ...
16
votes
3answers
3k 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, ...
6
votes
3answers
3k 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 ...