SQL statements to lock and unlock database tables

Lock is a very important concept in database, it is mainly used in multi-user environment to ensure database integrity and consistency. we know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if there is no lock and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. These issues include: missing updates, dirty reads, non-repeatable reads, and hallucination reads:

1. A missing update issue occurs when two or more transactions select the same row and then update the row based on the value originally selected. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who last saved its change copy overrides the changes made by the first editor. If the second editor can make changes after the first editor finishes, you can avoid the problem.

2. dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct. For example, an editor is changing an electronic document. During the change, another editor copied the document (which contains all the changes made so far) and distributed it to the intended user. After that, the first editor thought that the changes that were being made were wrong, and then deleted the edits and saved the document. Documents that are distributed to users contain edits that no longer exist, and those edits should be considered to have 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. non-repeatable reading refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read. 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 is not repeatable. You can avoid this problem if the editor can read the document only after the author has finished writing it all.

4. illusion reading refers to a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. For example, an editor changes the document submitted by the author, but when the production department merges its changes into the primary copy of the document, it finds 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 have finished processing the original document.

Therefore, the method of handling multiuser concurrent access is locking. locks are a primary means of preventing other transactions from accessing the specified resource control and implementing concurrency control. When a user locks an object in the database, other users can no longer access the object. the impact of locking on concurrent access is reflected in the granularity of the lock. In order to control the locked resources, you should first understand the system's space management. in a SQL Server 2000 system, the smallest space management unit is the page, and a page has 8 K. All data, logs, and indexes are stored on the page. In addition, the use of a page has a limit, which is that a row of data in a table must be on the same page, not across pages. The Space Management unit above the page is the disk area, and a disk area is 8 contiguous pages. The minimum unit of use for tables and indexes is the extents. A database is made up of one or more tables or indexes, which are made up of multiple

SQL statements:

LOCK TABLES tablename WRITE; LOCK TABLES tablename Read;insert into assignment VALUES (1, 7513, ' 0000-00-00 ', 5), (1, 7513, ' 2003-01-20 ', 8.5); UNLOCK TABLES;

For multiple users submitting a form at the same time and getting a form ID in the database, I resolved this:

mysql_query ("Lock tables PO read"), mysql_query ("Lock Tables Po write"), mysql_query ("Update po set id=id +1"));//Increase  PO id$sql = "SELECT id from po", $result = mysql_query ($sql), if ($row = Mysql_fetch_assoc ($result)) {    echo $row ["id"]; This order is the use of this id}mysql_free_result ($result), mysql_query ("Unlock Tables"), ..... ....... .........

Help to see how to write lock and unlock statements to the database before and after this piece of code

' (here to lock the database, how to write the lock statement?) )        ...        ...... i = str (get maximum inbound number from Recordset rst0)        ' if not locked, it is prone to concurrency conflicts. Because there is no time to establish the actual record after the application to the maximum incoming number, other clients may also apply for the same storage        number ... Rst.open "SELECT * from warehousing where inbound number =" + i + "and commodity id=0", CN, adOpenStatic, adLockOptimistic        If rst. RecordCount = 0 Then Next inbound number = i + 1        

' (Unlock the database here, how do I write the unlock statement?) ）

Answer 1: Declare the statement that needs to be executed as a transaction (such as 2 floor), then lock, the type of lock in SQL Server is many, see what kind of lock you need to add: HOLDLOCK #将共享锁保留到事务完成, instead of releasing the lock immediately when the corresponding table, row, or data page is no longer needed. HOLDLOCK is equivalent to SERIALIZABLE. NOLOCK #不要发出共享锁, and do not provide an exclusive lock. When this option is in effect, it is possible to read uncommitted transactions or a set of pages that are rolled back in the middle of the read. Dirty reads may occur. Applies only to SELECT statements. Paglock #在通常使用单个表锁的地方采用页锁. ReadCommitted #用与运行在提交读隔离级别的事务相同的锁语义执行扫描. By default, SQL Server 2000 operates at this isolation level. READPAST #跳过锁定行. This option causes the transaction to skip rows that are locked by other transactions, which are normally displayed in the result set, rather than blocking the transaction so that it waits for other transactions to release locks on those rows. The READPAST lock hint is only available for transactions that run at the commit read isolation level, and is read only after row-level locks. Applies only to SELECT statements. ReadUncommitted #等同于 NOLOCK.    RepeatableRead #用与运行在可重复读隔离级别的事务相同的锁语义执行扫描. Rowlock #使用行级锁 instead of using page-level and table-level locks that are more granular. SERIALIZABLE #用与运行在可串行读隔离级别的事务相同的锁语义执行扫描. Equivalent to HOLDLOCK. TABLOCK #使用表锁代替粒度更细的行级锁或页级锁. SQL Server holds the lock until the end of the statement. However, if you specify HOLDLOCK at the same time, the lock will be held until the end of the transaction. Tablockx #使用表的排它锁. The lock prevents other transactions from reading or updating the table and holding it until the end of the statement or transaction. UPDLOCK #读取表时使用更新锁 without a shared lock and retains the lock until the end of the statement or transaction. The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data at a later time, while ensuring that the data has not been changed since the last time the data was read. XLOCK #使用排它锁并一直保持到由语句处理的所有数据上的事务结束时. The lock can be specified using Paglock or TABLOCK, in which case the exclusive lock applies to the granularity of the appropriate level.

　　

Answer 2 "to make a table not be modified concurrently during the entire processing of the available transactions begin Transelect * FROM table name  with HOLDLOCK--processing statements .... Commit Tran Plus with," and " After Holdlock, people can't move your watch until the transaction is committed.

　　

。。。。。。。。。。。。。。。。。。。。。。。。。。。。

SQL statements to lock and unlock database tables