sql server services Raiders

2010-12-17  来源:本站原创  分类:Database  人气:93 

sql server services Raiders

Posted on :2007 -2-20 17:21:38 Source: unknown Views: Today: 1 Week: 14 Month: 28 Total: 237 TAG list: no tag yet

A transaction properties

Transaction with ACID properties

The Atomic Atomic, Consistent consistency, Isolated Isolation, Durable permanent

Atomic

Is the transaction should be used as a working unit, the transaction is completed, all of the work or are preserved in the database, either completely

Rollback, all the non-reserved

Consistency

After the transaction is completed or withdrawn, should be in a consistent state

Isolation

Multiple transactions simultaneously, should be no interference between them. Should be to prevent a transaction other matters have to modify the data,

Unreasonable access and read data incomplete

Permanent

After the transaction commits, the work was permanently preserved

Two concurrent transactions will have problems dealing with

Lost update

When two or more transactions select the same row, then update the value initially selected based on the row, lost update problem occurs,

Each transaction do not know the existence of other matters. Last update will override the other firm to do an update, which will lead to data loss.

Dirty read

When the second transaction select other transactions are updating the row, would not confirm the occurrence of related issues.

The second transaction is reading the data has not been confirmed and may update this line by the firm to change.

Non-repeatable read

When the second transaction accesses the same line several times and each reading different data, inconsistent analysis problems will occur.

Inconsistent with the analysis of a similar correlation is not confirmed, because other matters are also being changed the second transaction is reading data.

However, in inconsistent analysis, the second transaction reads the data has been changed by the transaction is committed. Also, inconsistent analysis involves multiple (two or more) read the same line, and other matters by the time the information changes; which the bank is non-repeatable reads.

Phantom Read

When a row insert or delete operation, and the line is part of a transaction is the scope of the line read occurs when the phantom read problem.

Transactions first read range of lines showing which line no longer read or follow-up on the second reading, because the row has been deleted by other transactions. Similarly, the insertion of other transactions, the transaction's second or subsequent reading shows a line that no longer exist in the original reading.

Three types of transaction

Automatic processing of transactions

Default T-SQL commands for each transaction are started automatically by the system and submit

Implicit Transaction

When a large number of DDL and DML commands will be executed automatically, and has been maintained up to the user to submit a clear, implicit transaction can be switched SET IMPLICIT_TRANSACTIONS

To set the implicit transaction mode to connect when set to ON, SET IMPLICIT_TRANSACTIONS set the connection to implicit transaction mode. When set to OFF, then the connection returns to autocommit transaction mode

User-defined transaction

Transaction by the user to control the start and end commands: begin tran commit tran rollback tran command

Distributed Transactions

Transactions across multiple servers as a distributed transaction, sql server by DTc microsoft distributed transaction coordinator

To support distributed transaction processing, you can use BEgin distributed transaction command to start a distributed transaction processing

Four transaction isolation level

Use SET TRANSACTION ISOLATION LEVEL to control all the statements issued by the connection the default transaction locking behavior

Is from low to high

READ UNCOMMITTED

Perform a dirty read isolation locking or 0, which means that no shared locks are issued, nor accept the exclusive lock. When setting this option, you can perform on the data read uncommitted or dirty read; before the end of the transaction value within the data can be changed, lines can also appear in the data set, or away from the data set. This option is the role and all the statements in the transaction on all tables in the same setting NOLOCK. This is the four isolation levels in the least restrictive level.

Example

Set table1 (A, B, C)

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

Two new connections

In the first statement following a connection

select * from table1

begin tran

update table1 set c = 'c'

select * from table1

waitfor delay '00: 00:10 '- wait 10 seconds

rollback tran

select * from table1

In the second connection, execute the following statement

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

print 'dirty read'

select * from table1

if @ @ rowcount> 0

begin

waitfor delay '00: 00:10 '

print 'not repeatable read'

select * from table1

end

The results of the second connection

Dirty read

ABC

a1 b1 c

a2 b2 c

a3 b3 c

'No repeat reading'

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

READ COMMITTED

Specified in the control of the shared locks when reading data in order to avoid dirty reads, but the data may change before the end of the transaction, resulting in non-repeatable reads or phantom data. This option is the SQL Server default.

In the first statement following a connection

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

begin tran

print 'initial'

select * from table1

waitfor delay '00: 00:10 '- wait 10 seconds

print 'not repeatable read'

select * from table1

rollback tran

In the second connection, execute the following statement

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

update table1 set c = 'c'

The results of the first connection

Initial

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

Do not repeat the read

ABC

a1 b1 c

a2 b2 c

a3 b3 c

REPEATABLE READ

Lock all the data used in the query to prevent other users from updating data, but other users can insert a new phantom rows of data sets, and the phantom line included in the follow-up reading in the current transaction. Because concurrency is lower than the default isolation level, it should only use this option only when necessary.

In the first statement following a connection

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

begin tran

print 'initial'

select * from table1

waitfor delay '00: 00:10 '- wait 10 seconds

print 'phantom read'

select * from table1

rollback tran

In the second connection, execute the following statement

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

insert table1 select 'a4', 'b4', 'c4'

The results of the first connection

Initial

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

Phantom Read

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

a4 b4 c4

SERIALIZABLE

In place a range of data sets lock to prevent other users update the data before the completion of the transaction set or insert rows into the dataset. This is the four isolation levels to limit the maximum level. Because the level of concurrency is lower, so should only use this option only when necessary. This option is the role and all the SELECT statement in the transaction on all tables in the same set HOLDLOCK.

In the first statement following a connection

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

begin tran

print 'initial'

select * from table1

waitfor delay '00: 00:10 '- wait 10 seconds

print 'no change'

select * from table1

rollback tran

In the second connection, execute the following statement

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

insert table1 select 'a4', 'b4', 'c4'

The results of the first connection

Initial

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

No change

ABC

a1 b1 c1

a2 b2 c2

a3 b3 c3

Five transaction nested syntax and the impact on the @ @ TRANCOUNT

BEGIN TRAN @ @ TRANCOUNT +1

COMMIT TRAN @ @ TRANCOUNT-1

ROLLBACK TR

-------------------------------------------------- -------------

Words Reviewing the new

相关文章
  • sql server services Raiders 2010-12-17

    sql server services Raiders Posted on :2007 -2-20 17:21:38 Source: unknown Views: Today: 1 Week: 14 Month: 28 Total: 237 TAG list: no tag yet A transaction properties Transaction with ACID properties The Atomic Atomic, Consistent consistency, Isolate

  • With SQL Server skills to learn DB2 UDB V8 2010-05-12

    With SQL Server skills to learn DB2 UDB V8 Introduction In today's information technology field, new information continues to impact us - the new software product, new versions and new features. So you can keep up with these constant changes of the h

  • SQL SERVER 2000 exercises. Doc 2010-12-10

    Overview of Problem SERVER 2000 1. The common version of SQL SERVER 2000 What? Of the respective applications is? a. Enterprise Edition: General, as the production database server. It supports SQL SERVER 2000 in all the available features. The versio

  • SQL Server large collection of practical experience and skills 2011-07-16

    Including the installation of a pending operation when prompted, shrink the database, compressed database, transfer the database to the new user to an existing user rights, check the backup set, repair the database. 1. Suspend operation Sql or sp pat

  • SQL Server samples directory 2011-07-21

    Section 1 of SQL Server 2005 installation, configuration and operation of Chapter 1 of SQL Server 2005 Installation and Configuration 2 1.1 Installing and Uninstalling SQL Server 2005 2 Example 001 SQL Server 2005 installation 2 Install the sample da

  • win7 下使用sql管理器连接sql server 2005方法大全(如果18452/18456错误,必看) 2014-05-03

    天在win7下安装好sql server express 2005,本地用户连接可以成功,但是尝试远程用ip登录,始终有错误,开始是18452,后来是18456,经过摸索,总结了一套配置流程,与大家分享如下: 1.首先确保sql server 服务已经启动(这个可以在服务中看到) 2.然后查看本地1433端口是否启动监听(用netstat -an)命令查看 2.1 如果没有启动,进入"sql server 配置管理器",点击"sql server 2005网络配置"

  • In Microsoft Windows XP to configure HTTP access to SQL Server 2005 Analysis Services 2010-10-13

    In Microsoft Windows XP to configure HTTP access to SQL Server 2005 Analysis Services Abstract <br /> this article for those who are on the use of HTTP protocol to access SQL Server Analysis Services interested. HTTP connection article gives all the

  • How To Use the Analysis Services Processing Task in SQL Server 2005 Integration 2010-11-11

    Transfer from the following: http://www.mssqltips.com/tip.asp?tip=1560 Problem We are looking to automate the processing of our SQL Server Analysis Services dimensions and cubes. We'd like to add this processing to our existing SQL Server Integration

  • [Hosted services] SQL Server 2011 new features 2011-05-20

    Learned friends for the database will not be unfamiliar with the software SQL Server, SQL Server 2005 has been called the developer version of SQL Server 2008 and later SQL Server 2008 R2 features are its rich developer has won approval . In November

  • Analysis Services in SQL Server 2008 new features 2011-07-29

    Analysis Services in SQL Server 2008 new features - In-depth SQL Server 2008 Introduction: This book covers SQL Server 2008 introduces new features. Robert E. Walters is the author of Microsoft SQL Server development team, project manager, in a relat

  • Services in SQL Server 2000 2011-04-24

    (1) type of service we can in the SQL Server Service Manager Service drop-down box to see all the services in SQL Server. Specifically, SQL Server 2000 services to a total of four kinds: A. SQL Server: This is the main service, but also of SQL Server

  • SQL Server 2005 Express remote connection client remote access settings Raiders (For other versions of SQL Server 2005) 2011-05-01

    Microsoft SQL Server 2005 Express is to provide low-end database solutions, with products distributed free of charge Renyi, in XP and other non-professional server system to install, remotely access applications for small data has been enough to meet

  • sql server 2005 Reporting Services Architecture _ in the database skills 2011-07-04

    Original address (http://www.west263.com/www/info/62075-1.htm) SQL Server Reporting Services (SSRS) is a comprehensive and extensible reporting platform, which includes an integrated set of processing components, programmatic interfaces and tools. In

  • SQL Server 2008 Reporting Services Anonymous Access Report 2011-10-06

    Implement SQL Server 2008 Reporting Services, there are two ways to access the report anonymously. First, SQL Server 2008 by modifying the configuration file, remove the Windows authentication. 1 First, we find two SQL installation directory Web.conf

  • SQL Server 2008 Reporting Services reports to achieve anonymous access 2011-10-06

    Implement SQL Server 2008 Reporting Services, there are two ways to access the report anonymously. First, SQL Server 2008 by modifying the configuration file, remove the Windows authentication. 1 First, we find two SQL installation directory Web.conf

  • SQL Server Integration Services 服务启动失败 2014-05-31

    WIN7 + SQLSERVER2012启动时报SQL Server Integration Services 服务启动失败,描述: 由于下列错误,SQL Server Integration Services 服务启动失败: 服务没有及时响应启动或控制请求. 查看一下服务 SQL Server Integration Services,在登录身份那里选了本地帐户,启动服务成功.

  • SQL Server JDBC Driver configuration 2008-10-03

    SQL Server JDBC Driver configuration keywords: sql server jdbc driver configuration method a and download drivers. Download: http://download.microsoft.com/download/8/B/D/8BDABAE2-B6EA-41D4-B903-7916EF3690EF/sqljdbc_1.2.2323.101_enu.exe First, downloa

  • jdbc & & sql server 2005 express 2010-03-06

    [] Http://download.csdn.net/source/1897285 transfer 1. Preparation: Prepare the relevant software (Eclipse, except open-source software can be downloaded from official website) <1>. Microsoft SQL server 2005 Express Edition Download: http://download

  • SQL Server 2005 to open a remote connection 2010-03-08

    By default, SQL Server Express Edition 2005 and SQL Server 2005 Developer Edition does not allow remote connections. To allow remote connections SQL Server 2005, need to complete the following steps: SQLServer instance in remote connections enabled.

  • SQL Server database password associated sa solution to the problem 2010-03-09

    Query Analyzer, connect, authenticate using the "use windows authentication" Then, run: EXEC sp_password NULL, 'NewPassword', 'Sa' Enterprise Manager -> Instance Name -> Security -> Login -> Double-click to the right of SA -> Chan