![]() ![]() Myths and Misconceptions about Transaction Isolation Levels |(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.). In todays blog posting I want to talk about myths & misconceptions about the various Transaction Isolation Levels that exists in SQL Server. I mainly want to talk about the following topics: Transaction Isolation Levels – What? NOLOCK never blocks!? Read Committed doesn’t holds locks!? Key Range Locks are specific to Serializable!? I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM <tablename> FOR UPDATE. So, here what’s the data propagation chain is: When user changes a logistics document, changed data are propagated to inventTrans; Then in the end of transaction. So let’s get started by first laying out the foundation about Transaction Isolation Levels in SQL Server. Transaction Isolation Levels – What? Everytime when I’m on customer side, and troubleshooting crazy SQL Server problems (or performing SQL Server Health Checks), the root cause sometimes lies in the locking/blocking behavior of the database. When you have one bad query (maybe you are missing a very important index), the performance of your whole database can go down. Have you ever tried starting a new transaction (without ever committing it) that acquires an exclusive lock the main table of your production database? Trust me: the performance and the throughput of your database will just die – in front of your eyes!!! ![]() So my Pro Tip to you: don’t try it ?When we are talking about locking & blocking in any relational database, we also have to talk about the various Transaction Isolation Levels that the DBMS supports (in our case – SQL Server). SQL Server supports 2 kinds of concurrency models: the “old” pessimistic one, and the “new” optimistic one that was introduced with SQL Server 2. Today I want to concentrate on the “old” one. In the old pessimistic concurrency model, SQL Server supports 4 different Transaction Isolation Levels: Read Uncommitted. Read Committed. Repeatable Read. Serializable. I don’t want to go into the deep internals about every Transaction Isolation Level, but I want to give you a brief overview what every Isolation Level is doing internally, because we need that information for the subsequent sections of this blog posting. When we are looking on a high level view onto SQL Server, you have transactions that are reading data (SELECT queries), and you have transactions that are changing data (INSERT, UPDATE, DELETE, MERGE). Every time when you are reading data, SQL Server has to acquire Shared Locks (S). Everytime when you change data, SQL Server has to acquire Exclusive Locks (X). And both locks are incompatible to each other, means readers are blocking writers, and writers are blocking readers. With the Transaction Isolation Level you can control now, how long a reader will hold its S locks. Writers will always acquire X locks, and you can’t influence them. By default SQL Server uses the Isolation Level Read Committed, which means SQL Server acquires a S lock, when you read a record, and and when the record was read, the S lock is released. When you are reading row by row, you are acquiring and releasing the needed S locks row by row. When you don’t want that readers are acquiring S locks (which isn’t really recommended), you can use the Isolation Level Read Uncommitted. Read Uncommitted just means you are able to read dirty data – data that isn’t yet committed. It’s blazingly fast (no one else can block you), but on the other hand it’s very dangerous, because it’s uncommitted data. Just think about, what happens if the uncommitted transaction is aborded after you have read the data: you have data in your hand that doesn’t even logically existed in your database. Your hands are very dirty now. I’m seeing a lot of cases during my consulting engagements where people are using Read Uncommitted or the NOLOCK query hint to get rid of blocking situations in SQL Server. But that’s not the preferred way to handle blocking. And as you will see later, even NOLOCK can block…In Read Committed you can have so- called Non Repeatable Reads, because someone else can change the data, when you are reading the data twice in your transaction. If you want to avoid Non Repeatable Reads, you can use the Isolation Level Repeatable Read. In Repeatable Read SQL Server holds the S locks until you end your transaction with a COMMIT or ROLLBACK. Means nobody else can make changes to your read data, and you are getting Repeatable Reads for your transaction. Serious Sam Hd The First Encounter Steam Cracker on this page. In every discussed Isolation Level so far, you are also always able to get so- called Phantom Records – records that can appear and disappear in your result set. If you want to get rid of these phantom records, you have to use the Isolation Level Serializable, which is the most restrictive one. In Serializable SQL Server uses a so- called Key Range Locking to eliminate phantom records: you are locking complete ranges of data, so that no other concurrent transactions can insert other records to prevent phantom records. As you can see from this description, the more restrictive your Isolation Level will be, the more it will hurts the concurrency of your database. So you have to choose the right Isolation Level very wisely. Running always in Read Committed doesn’t make sense, running always in Serializable doesn’t make sense. It depends, as usual ?By now I have laid out the foundation about Transaction Isolation Levels in SQL Server, and now I will show you 3 different cases, where my descriptions from above will not match. In some specific cases SQL Server has to guarantee the correctness of your transactions by changing the Transaction Isolation Level for specific SQL statements under the hood – without any intervention from you. Let’s start…NOLOCK never blocks!? As I have described in the introduction, the NOLOCK query hint also prevents for a specific SQL statement that S locks are acquired during the reading of records. This will make your SQL statements very fast, because the SELECT query can’t be blocked by any other transaction. I’m also referring to NOLOCK as the Turbo- Booster in SQL Server. But unfortunately NOLOCK can also block when you are dealing with concurrent DDL statements (Data Definition Language) like ALTER TABLE. Before we understand this behavior we need to have a more detailed look on DDL statements, and what happens internally in SQL Server, when we are running a simple SELECT query. When you are changing a table with the ALTER TABLE DDL statement, SQL Server acquires a so- called Schema Modification Lock (Sch- M) on that table. When you are running now at the same time a simple SELECT query against the same table, SQL Server has to compile in the first step a physical execution plan. And during the compilation phase SQL Server needs a so- called Schema Stability Lock (Sch- S). And both lock (Sch- M and Sch- S) are just incompabile to each other! This means that even a NOLOCK statement can block, because in the first step you have to compile an execution plan. So your NOLOCK statement blocks even before SQL Server knows the physical execution plan. Have you ever thought about that behavior when you have upgraded your database schema in production? Just think about it… Let’s demonstrate this behavior with a simple example. In the first step I’m creating a new table and inserting some records into it. Create a new test table. CREATE TABLE Test. Table. - - Insert some test data. DECLARE @i INT = 0. WHILE (@i < 1. INSERT INTO Test. Table VALUES (@i, @i + 1, @i + 2). GO1. 23. 45. 67. 89. Create a new test table. CREATETABLETest. Table(Column. INT,Column. 2INT,Column. INT)GO- - Insert some test data. DECLARE@i. INT=0. WHILE(@i< 1. 00. BEGININSERTINTOTest. Table. VALUES(@i,@i+1,@i+2)SET@i+=1. ENDGOAnd afterwards we are just starting a new transaction and and adding a new column to our table by executing a ALTER TABLE DDL statement. Begin a new transaction and do some work. BEGIN TRANSACTION. Add a new column. DDL statements require a Sch- M lock on the objects that are modified. In this case, the table "Test. Table" gets a Sch- M lock (Schema modification lock)! ALTER TABLE Test. Table ADD Column. INT- - Begin a new transaction and do some work. BEGINTRANSACTION- - Add a new column- - DDL statements require a Sch- M lock on the objects that are modified.- - In this case, the table "Test. Table" gets a Sch- M lock (Schema modification lock)! ALTERTABLETest. Table. ADDColumn. 4INTAs you can see from the previous code, the transaction is still ongoing, and isn’t yet committed. Select Energy Services Enters Into Definitive Agreement With Rockwater Energy Solutions In Stock- For- Stock Merger Transaction- Further strengthens one of the leading water solutions companies across all unconventional basins- Highly complementary strengths across pre- frac and other water- related service lines plus addition of completion and production chemicals business- Strong, unlevered balance sheet allows financial flexibility for continued infrastructure development projects and acquisition opportunities- Conference call is scheduled for Wednesday, July 1. Eastern Time. GAINESVILLE, Texas, July 1. PRNewswire/ - - Select Energy Services, Inc. WTTR) ("Select"), a leading provider of total water solutions to the U. S. unconventional oil and gas industry, today announced that it has entered into a definitive merger agreement with privately- held Rockwater Energy Solutions, Inc. Rockwater") in a stock- for- stock transaction. Select will issue 3. Rockwater common stock. Upon consummation of the transactions, current Select shareholders will own approximately 6. Rockwater shareholders will own approximately 3. Select common shares outstanding. Crestview Partners will be the largest shareholder post- merger, followed by SCF Partners and Mr. John Schmitz, currently Chairman and CEO of Select. The requisite stockholders of Select and Rockwater have approved the transactions, and the transaction is expected to close in the third quarter of 2. U. S. governmental approval under the Hart- Scott- Rodino Act. Rockwater is a leading provider of comprehensive water management solutions to the North American unconventional oil and gas industry, providing complementary chemical products and expertise related to water solutions. Rockwater's water management solutions include water sourcing, transfer, treatment and storage; flowback and well testing; fluids conditioning and recycling; and field fluids logistics. Rockwater also develops and manufactures a full suite of specialty chemicals used in the well completion process and production chemicals used to enhance performance over the producing life of a well. Upon completion of the merger, John Schmitz, currently Chairman & CEO of Select, will become the full- time Executive Chairman and Holli Ladhani, currently the Chairman, President & CEO of Rockwater, will become the President & CEO of Select. All water- related services of the combined company will consolidate under the Select brand name and Rockwater's chemicals business unit will retain the Rockwater brand name. Commenting on the merger, Mr. Schmitz said, "This is a very exciting opportunity to combine two companies that are highly- focused on the challenge of providing world- class water- related services to the major shale basins. As we are experiencing a strong recovery in many of our markets, we have the opportunity to combine our equipment, management teams, and over 3,2. I believe the combined expertise and scale we are creating in this merger will generate substantial benefits for Select and our shareholders."Ms. Ladhani added, "We are combining two entrepreneurial companies with similar cultures and aspirations. Our service lines are very complementary and as the industry continues to relentlessly increase frac intensity, the demand for creative solutions to water- related issues is expected to grow significantly as well. We intend to be at the forefront in providing those solutions."The two companies have updated their internal financial projections as part of the merger analysis. Rockwater's stand- alone revenue and adjusted EBITDA for the three- month period ended June 3. Select's stand- alone revenue and adjusted EBITDA for the three- month period ended June 3. Rockwater's stand- alone adjusted EBITDA over the second half of 2. The combination is expected to be meaningfully accretive to earnings per share, free cash flow per share and EBITDA per share on a fourth quarter 2. The combined company's pro- forma annualized revenue based on expected second quarter 2. Select's Peak and Affirm business units. Additionally, the combined company expects to realize consolidation benefits in a range of $1. From a balance sheet perspective, the combined company will be in a strong position with estimated pro forma net bank debt outstanding at June 3. Concurrent with the signing of the merger agreement, Select has received committed financing for a five- year $1. ABL) facility from one of its current lenders. In connection with the closing of the merger, the Company expects to increase the size of the facility to $3. The merger agreement provides for the expansion of the Select Board of Directors to nine members, adding four directors from the current Rockwater Board of Directors to join the five current members of the Select Board. The Audit and Compensation Committees will be chaired by existing Select Board members while the Nominating & Governance Committee will be chaired by a Rockwater nominated Director. Following the consummation of the transaction, Select will file a Registration Statement covering the Select common shares to be issued to the existing holders of Rockwater Class A- 1 common stock. As soon as that Registration Statement becomes effective, such shares will automatically convert to Class A common shares of Select. Simmons & Company International, Energy Specialists of Piper Jaffray & Co., acted as exclusive financial advisor and Vinson & Elkins LLP acted as legal advisor to Select in the transaction. Wells Fargo Securities, LLC acted as exclusive financial advisor and Baker Botts LLP acted as legal advisor to Rockwater. Conference Call. Select and Rockwater have scheduled a conference call on Wednesday, July 1. EDT to discuss the merger. Please dial 2. 01- 3. Select- Rockwater Merger Conference Call at least 1. Internet by logging on to the web at the address http: //investors. An accompanying slide presentation to the call will also be located at the same website address. A telephonic replay of the conference call will be available through July 2. A webcast archive will also be available at the link above shortly after the call and will be accessible for approximately 9. About Select Energy Services, Inc. Select Energy is a leading provider of total water solutions to the U. S. unconventional oil and gas industry. Select provides for the sourcing and transfer of water (both by permanent pipeline and temporary pipe) prior to its use in the drilling and completion activities associated with hydraulic fracturing, as well as complementary water- related services that support oil and gas well completion and production activities, including containment, monitoring, treatment, flowback, hauling, and disposal. For more information, please visit http: //selectenergyservices. About Rockwater Energy Solutions, Inc. Rockwater Energy Solutions is a leading provider of comprehensive water management solutions to the North American unconventional oil and gas industry and believes it is the only company that provides complementary chemistry products and expertise in connection with its water solutions. Rockwater's water management solutions include water sourcing, transfer, treatment, testing, remote monitoring, and storage; site and pit surveys; flowback and well testing; fluids conditioning and recycling and field fluids logistics. It also develops and manufactures a full suite of specialty chemicals used in the well completion process and production chemicals used to enhance performance over the producing life of a well, which the company believes gives it a unique competitive advantage in the energy industry. The company currently provides services to exploration and production companies and oilfield service companies operating in all the major shale and producing basins in North America, including the Permian Basin, the Mid- Continent (including the SCOOP/STACK plays), the Bakken, Western Canada, the Marcellus and Utica basins, the Rockies and the Eagle Ford. For more information, please visit http: //www. Cautionary Statement Regarding Forward Looking Statements. All statements in this communication other than statements of historical facts are forward- looking statements which contain our current expectations about our future results. We have attempted to identify any forward- looking statements by using words such as "expect", "will", "estimate" and other similar expressions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |