The tag has no usage guidance.

learn more… | top users | synonyms

1
vote
2answers
158 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 ...
0
votes
0answers
38 views

SQL Server Repeatable read locking [on hold]

I'm trying to understand the way the repeatable read isolation level works. I've been reading many articles and posts and still can't find my answer. Everywhere that repeatable read is mentioned, ...
1
vote
1answer
61 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 ...
3
votes
2answers
396 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
82 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 ...
0
votes
0answers
34 views

How system resolves when particular resource lock at uncommitted transaction

Let us assume Transaction T1#: Initiated on 2015-10-30 10:25:41.380 Transaction T2#: Initiated on 2015-10-30 10:25:41.580 The above two transactions are initiated through application. T1# & ...
6
votes
2answers
166 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 ...
4
votes
1answer
45 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 ...
2
votes
0answers
35 views

SQL Server Performance : isolate different databases under same engine

I have SQL Server 2014 installed onto a machine with two different users and two catalogs, each of them accessed by a different user (each user is strictly tied to his catalog). Is it possible that a ...
3
votes
1answer
99 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
1answer
167 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 ...
0
votes
0answers
22 views

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle?

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle? I need to implement several big selects to DB2 and also I need to retrieve consistent data which was commited on time when ...
3
votes
1answer
3k 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 ...
2
votes
1answer
314 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
147 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 ...
1
vote
0answers
109 views

debugging PostgreSQL serialization failures

I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format: ...
0
votes
2answers
94 views

When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF [closed]

SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01. The ...
0
votes
0answers
33 views

Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

Let's say we have a big table with hundreds of million of rows (or more). On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level: SELECT * FROM ...
2
votes
1answer
410 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 ...
3
votes
0answers
109 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 ...
6
votes
1answer
1k 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 ...
0
votes
1answer
338 views

Sybase ASE lock table to disallow users insert values

I need to disallow users to insert values in some table, for example for a 5 mins. Seems I should use at isolation clause but can't figure out how to use it. Thanks
3
votes
1answer
171 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: ...
1
vote
0answers
31 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
312 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
76 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
165 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
37 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
42 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
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
202 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 ...
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 ...
5
votes
1answer
615 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
138 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 ...
1
vote
1answer
397 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
870 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
1k 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
123 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 ...
8
votes
3answers
754 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, ...
4
votes
1answer
147 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
220 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
152 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
78 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
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 ...
1
vote
1answer
311 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
911 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
2k 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
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 ...
17
votes
2answers
14k 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
36 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. ...