concurrency Problems in Sqlserver2000

Concurrency problems in the Sqlserver2000 of server|sqlserver| problem One, concurrency problem generation:
If there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Concurrency issues include:
Missing or overwritten updates. Unconfirmed dependencies (dirty reads). Inconsistent analysis (non-repeat read). Phantom Reading. 1. Missing updates: When two or more transactions select the same row and then update the row based on the value originally selected, a loss update problem occurs. Every transaction has no knowledge of the existence of other transactions. The final update overrides updates made by other transactions, which results in data loss.
For example, two editors made electronic copies of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who finally saved its change copy overwrites the changes made by the first editor. This problem can be avoided if the second editor is able to make changes after the first editor finishes.
2, unconfirmed dependencies (dirty Read)
An unconfirmed dependency problem occurs when the second transaction selects rows that are being updated by another transaction. The data being read by the second transaction has not been confirmed and may be changed by the transaction that updated the row.

For example, an editor is changing an electronic document. During the change process, another editor copies the document (which contains all the changes made so far) and sends it to the intended user. After that, the first editor decided that the current changes were wrong, and then deleted the edits and saved the document. The document that is distributed to the user contains edits that no longer exist, and those edits should be considered never existed. This problem can be avoided if no one is able to read the changed document until the first editor determines the final change.
3. Inconsistent analysis (non-repeat reading)
An inconsistent analysis problem occurs when the second transaction accesses the same row multiple times and each time a different data is read. Inconsistent analysis is similar to unconfirmed dependencies because other transactions are also changing the data that the second transaction is reading. In an inconsistent analysis, however, the data read by the second transaction is committed by a transaction that has changed. Also, inconsistent analysis involves reading the same row multiple times (two or more), and each information is changed by another transaction, and the row is not read repeatedly.

For example, an editor reads the same document two times, but between two reads, the author rewrites the document. When the editor reads the document for the second time, the document has changed. The original read cannot be duplicated. You can avoid this problem if the editor can read the document only after the author has finished writing it all.
4. Phantom Reading
A phantom read problem occurs when an INSERT or delete operation is performed on a row that belongs to the range of rows being read by a transaction. The row range for the first read of the transaction shows that one row has ceased to exist in the second or subsequent reads because the row was deleted by another transaction. Similarly, because of the insert operation of another transaction, the second or subsequent read of the transaction shows that a row does not exist in the original read.

For example, an editor changes the document submitted by the author, but when the production department merges its changes to the master copy of the document, it discovers that the author has added the unedited new material to the document. This problem can be avoided if no one is able to add new material to the document until the editor and production department finish processing the original document.
Second, the solution to the problem of concurrency:
When locking is used as a concurrency control mechanism, it can resolve concurrency problems. This allows all transactions to run in an environment that is completely isolated from each other, but can have more than one running transaction at any time.

Serializable is the state of the database that is reached by running a set of concurrent transactions, and is equivalent to the status of the databases that this set of transactions is performing sequentially in some order.
SQL-92 Isolation Level
Although serializable is important for transactions to ensure that the data in the database is correct at all times, many transactions do not always require complete isolation. For example, multiple authors work in different chapters of the same book. New chapters can be submitted to the project at any time. However, for chapters that have already been edited, the author cannot make any changes to this section without the approval of the editor. In this way, although there are new chapters that are not edited, editors can still ensure the correctness of the book item at any time. Editors can view previously edited chapters and recently submitted chapters.

The level at which a transaction prepares to accept inconsistent data is called the isolation level. The isolation level is the degree to which a transaction must be isolated from other transactions. Lower isolation levels can increase concurrency, but at the cost of reducing data correctness. Conversely, a higher isolation level ensures that data is correct, but can have a negative impact on concurrency. The isolation level that the application requires determines the locking behavior that SQL Server uses.

SQL-92 defines the following four isolation levels that SQL Server supports for all of these isolation levels:
Uncommitted reads (the lowest level of transaction isolation, which guarantees that no physical corrupted data is read). Commit read (SQL Server default level). can be read repeatedly. Serializable reads (highest level of transaction isolation, complete isolation between transactions).
If a transaction runs at the Serializable isolation level, any concurrent overlapping transactions can be guaranteed to be serial.

The following four isolation levels allow different types of behavior.

Isolation level

Dirty Read

Non-repeatable reads

Phantom image





Read not submitted

Is

Is

Is

Submit Read

Whether

Is

Is

REPEATABLE READ

Whether

Whether

Is

can be read serially

Whether

Whether

Whether



Transactions must run at a repeatable read or higher isolation level to prevent loss of updates. A lost update occurs when two transactions retrieve the same row and then update the row based on the value of the original retrieved. If two transactions update rows with an UPDATE statement and are not updated based on previously retrieved values, no loss updates occur at the default commit read isolation level.

III. implementation of problem solving:
1. Default solution: SET TRANSACTION Isolation Level
Controls the default transaction lockout behavior for all Microsoft®sql server™select statements issued by a connection.
Grammar
SET TRANSACTION Isolation Level
{READ Committed
| READ UNCOMMITTED
| Repeatable READ
| SERIALIZABLE
}
Parameters
READ committed

Specifies that the shared lock is controlled when reading data to avoid dirty reads, but the data can be changed before the transaction ends, resulting in unreadable or phantom data. This option is the default value for SQL Server.

READ UNCOMMITTED

Performs a dirty read or level 0 isolation lock, which means that no shared locks are issued and exclusive locks are not accepted. When this option is set, uncommitted read or dirty reads can be performed on the data, and values within the data can be changed before the transaction ends, and rows can also appear in the dataset or disappear from the dataset. This option has the same effect as setting NOLOCK on all the tables in all statements within a transaction. This is the least restrictive level in the four isolation levels.

Repeatable READ

Locks all the data used in the query to prevent other users from updating the data, but other users can insert new Phantom rows into the dataset, and phantom rows are included in subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, you should only use this option if necessary.

SERIALIZABLE

Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction completes. This is the most restrictive level in the four isolation levels. Because the concurrency level is low, this option should be used only if necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements within a transaction.
Comments
Only one of these options can be set at a time, and the options you set will remain in effect for that connection until you explicitly change the option. This is the default behavior unless you specify optimization options at the table level in the statement's FROM clause.

Set TRANSACTION isolation level settings are set at execution or run time, not at parse time.
Example
The following example sets the TRANSACTION isolation level for the session. For each subsequent Transact-SQL statement, SQL Server controls all shared locks until the end of the transaction.

SET TRANSACTION Isolation Level repeatable READ

Go

BEGIN TRANSACTION

SELECT * FROM Publishers

SELECT * FROM Authors

...

COMMIT TRANSACTION
2. Custom Transaction ISOLATION LEVEL
By default, Microsoft®sql server™2000 operates at an isolation level in the READ committed. However, an application may have to run at different isolation levels. To use a more restrictive or looser isolation level in your application, you can customize the locking of the entire session by using the SET TRANSACTION isolation LEVEL statement to set the isolation levels of the session.

When the isolation level is specified, the locking behavior of all SELECT statements in the SQL Server session runs at that isolation level and remains valid until the session terminates or sets the isolation level to another level. For example, to set the transaction isolation level to serializable to ensure that concurrent transactions cannot insert phantom rows in the authors table, do the following:

Use pubs

Go

SET TRANSACTION Isolation Level SERIALIZABLE

Go

BEGIN TRANSACTION

SELECT au_lname FROM Authors



Note If necessary, you can override the isolation level of a single SELECT statement by specifying a table-level locking hint. Specifying a table-level lock hint does not affect other statements in the session. It is recommended that you use table-level locking hints to change the default locking behavior only when absolutely necessary.

To determine the transaction isolation level that is currently set, use the DBCC useroptions statement, for example:

Use pubs

Go

SET TRANSACTION Isolation Level repeatable READ

Go

DBCC useroptions

Go

Set Option

Value

Textsize

4096

Language

Us_english

DateFormat

Mdy

Datefirst

7

Isolation level

REPEATABLE READ



(5 rows affected)

DBCC execution completed. If DBCC Printed error messages, the your System Administrator.
3. Adjust Transaction ISOLATION Level
The Quarantine property is one of the four properties of ACID, and the logical unit of work must have these four properties to be called a transaction. This property enables transactions to be protected from updates performed by other concurrent transactions. The isolation level of each transaction can actually be customized.

Microsoft®sql Server™ supports the transaction isolation level defined in SQL-92. Setting the transaction isolation level allows programmers to take the risk of some integrity issues, but in return for greater concurrent access to the data. Each isolation level provides greater isolation than the previous isolation level, but it is swapped for more restrictive locks over a longer period of time. Transaction isolation levels are:
READ Uncommittedread committedrepeatable readserializable
Transaction isolation levels can be set using Transact-SQL or through the database APIs:

Transact-SQL

Transact-SQL scripts and db-library applications use the SET TRANSACTION isolation level statements.

Ado

The ADO application sets the IsolationLevel property of the Connection object to adxactreaduncommitted, adxactreadcommitted, Adxactrepeatableread, or Adxactreadserializable.

OLE DB

The OLE DB application invokes ITransactionLocal::StartTransaction, where Isolevel is set to isolationlevel_readuncommitted, Isolationlevel_ ReadCommitted, Isolationlevel_repeatableread or isolationlevel_serializable.

Odbc

The ODBC application invokes SQLSetConnectAttr, where Attribute settings are set to Sql_attr_txn_isolation,valueptr set to sql_txn_read_uncommitted, Sql_txn_ read_committed, Sql_txn_repeatable_read or sql_txn_serializable.

Reference <<sqlserver2000 Help file > >