The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
1answer
527 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
1
vote
0answers
56 views

NOLOCK vs Read Uncommitted

in our environment, reports are running on the subscriber and resulting in deadlocks with MS Replication SPs, which are doing inserts/updates. What's the best way to eliminate the deadlocks? We have ...
0
votes
1answer
44 views

MySQL MVCC implementation

The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table: We have a relatively large table which we periodically scan reading several fields including a field named, ...
0
votes
1answer
68 views

Read Committed Snapshot Isolation vs Read Committed - pros and cons?

We have an application from JD Edwards, for which the database runs on SQL Server 2005 or 2008R2. They have a new application vendor and he suggested to change isolation level to Read Committed ...
0
votes
1answer
15 views

Forcing uncommitted read access via view?

I have an application (which I'm only the user of, i.e. I cannot change it, but I do have control over the database) that performs relatively simple, but potentially very frequent concurrent look-ups ...
4
votes
1answer
59 views

Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
2
votes
2answers
127 views

Blocking continues after enabling snapshot isolation

On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using SELECT snapshot_isolation_state_desc,name from sys.databases However, in ...
1
vote
1answer
64 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 [dbo].[...
0
votes
1answer
34 views

Which isolation level do I need?

I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database. This is my situation: Let's say I have two group of users: Group A (...
1
vote
1answer
37 views

Innodb Isolation levels and locking performance

I am reading a lot about locking and isolation levels in MySQL and InnoDB. I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ ...
2
votes
1answer
72 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 ...
0
votes
0answers
13 views

Isolation Level Impact on Selecting a Row Moving Between Two Tables

I have two tables (identical structure, one for current data and one for archive) being used as a source for a datamart staging table. I construct a SSIS data flow that runs SELECTs on both tables ...
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) ...
2
votes
2answers
190 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 ...
1
vote
0answers
45 views

SQL Server Repeatable read locking [closed]

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
98 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
427 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
102 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 ...
8
votes
2answers
782 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 ...
2
votes
1answer
224 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 statement-...
0
votes
0answers
35 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
297 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
54 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
192 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
172 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 ...
4
votes
1answer
6k 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 understanding,...
2
votes
1answer
506 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) ...
0
votes
2answers
99 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 ...
1
vote
0answers
181 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: ...
1
vote
0answers
62 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 bigtable;...
2
votes
1answer
542 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
4answers
492 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 ...
3
votes
0answers
114 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
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 ....
4
votes
1answer
219 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
34 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
345 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
91 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
192 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
38 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
44 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 ...
8
votes
3answers
906 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, ...
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
231 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
681 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 http://...
1
vote
0answers
173 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
2k 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 ...
2
votes
1answer
566 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 ...