PB multiple clients connect to the server database-CodePudding

Niche with the following code, the client is connected to the LAN of another computer, open a client, can in normal operation, open the two, one is no response, what is this, is there want to change the code, how to change, a hand warrior,
//Profile ydf
The SQLCA. DBMS="OLE DB"
The SQLCA. LogId="sa"
The SQLCA. The AutoCommit=true
='SQLOLEDB' SQLCA. DBParm="PROVIDER and DATASOURCE='192.168.31.163', PROVIDERSTRING='database=BYD2015'"
Connect using the sqlca;

CodePudding user response:

refer to the original poster qq_29841149 response:

niche with the following code, the client is connected to the LAN of another computer, open a client, can in normal operation, open the two, one is no response, what is this, is there want to change the code, how to change, a hand warrior,
//Profile ydf
The SQLCA. DBMS="OLE DB"
The SQLCA. LogId="sa"
The SQLCA. The AutoCommit=true
='SQLOLEDB' SQLCA. DBParm="PROVIDER and DATASOURCE='192.168.31.163', PROVIDERSTRING='database=BYD2015'"
Connect using the sqlca;

This is connect to the database to connect several should be no problem, basically be to see the database set the maximum number of connections, you estimate is not here, is out of the other code

CodePudding user response:

The sqlca. Lock='RC'

CodePudding user response:

The problem in SQL SERVER call blocking, is not really a deadlock,
Reason is caused by several factors:
1, the main reason is the default SQL SERVER is using a read the commit, and the ordinary procedure using lock level is read uncommit. Use the read uncommit (i.e., dirty reads) will influence the consistency of the data, but not all processing need lock, because your program is old, so I think there is no transaction careful analysis of the need to lock, cause every read data should be locked, the lock and other SELECT lock, and UPDATE the UPDATE lock conflict, cause the program to wait for each other, especially PB DW, either don't use transactions, or use the transaction is longer, so if a man not to close his DW, SELECT the data and the DW in a long transaction, the others had to wait for wait for, this lock is more more, had to crash,
2, the second reason is similar to the first reason, but generally occurs in a table similar to the code table, because this type of table is frequently accessed, thus formed the processing time, a very, very hot, as one of the SELECT lock is easy to happen here, is therefore more likely to cause congestion, in addition, for smaller hot table, SQL SERVER for performance considerations of reason, in the case of frequently accessed, line will automatically lock escalation to table locks, once upgraded to table locks, other process to access the table had to die,

Processing method is very simple:
1, not all the places you need to use the read the commit of the level of locking, you set a sqlca. From application lock="RU", using dirty read, so you can remove most of the SELECT row locks, then be sure to read the latest data, the sqlca, change the lock to RC, change back after using it,
This avoids the almost 80% of blocking,
2, the due to the row updates, or other caused by the UPDATE locks, general database will coordinate, the transaction is longer, it requires you to make appropriate modifications to the original procedures and the long transaction into several small transactions, do UPDATE operations in the transaction, do not insert the user interaction, it is a principle of the system design,
If your system is not strict with affairs, do not want to change the original program, more simple, in the previous
The SQLCA, on the basis of the LOCK, and other SQLCA, the AUTOCOMMIT=TRUE, so that each data change automatically submit, can avoid most deadlocks and obstruction due to update,
3, the last large amount is just said to deal with the frequently accessed TABLE (HOT TABLE), if your system allows the use of level of RU lock, so need not consider too, because SELECT already does not lead to lock,
But if you can't use RU way mentioned method is in (1),
To adopt this approach:
Using the index update locks, SELECT lock to separate, also avoid essentially a silly row locks for performance reasons to upgrade to table locks,
Specific measure is to build an index, if you can use clustered index, because the clustered index is similar to a HASH retrieval method, so that when the search index, there is no need for access to the data table,
Is another way, will you SELECT statement to retrieve the data is added to the index, for example, you retrieve the NAME, SEX, AGE, if you put the three joined the index data, this means that the SELECT statement as long as find index, have been found finally to SELECT data (from the index), such natural won't go to the LOCK table, do it for you when you need to carefully choose the index procedure, otherwise the index into the table, I had a backup,

To summarize, just three recruit:
1, reduce the level of lock, 2, 3, possible use of automatic adopt measures to prevent hot table access
When necessary, of course, to optimize your application, is also important to carefully divided the transaction,

CodePudding user response:

About the lock in MS SQL attribute
RU the Read Uncommitted
RC Read Committed
The RR Repeatable Read
TS Serializable
Refer to the SQL SERVER2005 books online has the following description:
SQL server database engine isolation level
SQL - 99 standard defines the following isolation levels, Microsoft SQL Server Database Engine supports all this isolation level:
Uncommitted read (the lowest level of isolation affairs, can only guarantee is not physically damaged data read)
Has been submitted to read (the default level of database engine)
Repeatable read
Serializable (the highest level in isolation transaction, the transaction between completely isolated)
Practical tests show that when setting the sqlca. Lock='RR' repeatable read, there will be a deadlock, the other three won't, seem to be PB10 above and the default sqlca. Lock='RR' would cause a deadlock,
Is to use "RC", generally low version of the PB with MSS when connecting the default level, suitable for most of the time, of course also depends on your specific application

CodePudding user response:

reference 4 floor lzp_lrp response:

about the lock in MS SQL attribute
RU the Read Uncommitted
RC Read Committed
The RR Repeatable Read
TS Serializable
Refer to the SQL SERVER2005 books online has the following description:
SQL server database engine isolation level
SQL - 99 standard defines the following isolation levels, Microsoft SQL Server Database Engine supports all this isolation level:
Uncommitted read (the lowest level of isolation affairs, can only guarantee is not physically damaged data read)
Has been submitted to read (the default level of database engine)
Repeatable read
Serializable (the highest level in isolation transaction, the transaction between completely isolated)
Practical tests show that when setting the sqlca. Lock='RR' repeatable read, there will be a deadlock, the other three won't, seem to be PB10 above and the default sqlca. Lock='RR' would cause a deadlock,
Is to use "RC", generally low version of the PB with MSS when connecting the default level, suitable for most of the time, of course, also look at your specific application

Montana moderators, explain very clearly, the collection

CodePudding user response:

May be carried out statements have not been submitted, lead to lock

CodePudding user response:

You have less a parameter
The sqlca. Lock='RC'